Environment:
Central SQL 2000 db (with SP3a), employing 3 Merge Publications with column
level tracking
240 distributed/disconnected subscribers using pull subscriptions running
MSDE 2000
We're having a high volume of suspect databases appearing. Causes are varied
but the majority are torn pages. None are due to running out of disk space.
Currently our fix is to drop the database & rebuild it using replication.
However this is pretty slow. So alternatively, if we took daily backups of
each subscriber db, would a simple restore make everything work again?
If we take a backup at 1am, the user syncs at 9am and then subsequently the
database becomes suspect & we need to restore the backup. Will the
subscriptions continue to work and will the data that was sync'd at 9am be
retransmitted down to the subscriber?
Does anyone foresee any other problems?
thanks,
Jonathan
I think you should address your torn pages problem. For instance it could be
a disk, controller, or a power purity problem.
To address your question. If you restore the backup to the subscriber, the
publisher will fill in the missing data since 9:00 am.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jonathan Ainsworth" <JonathanAinsworth@.discussions.microsoft.com> wrote in
message news:55C49FD8-2BBC-406E-9815-0EFAD17268B6@.microsoft.com...
> Environment:
> Central SQL 2000 db (with SP3a), employing 3 Merge Publications with
column
> level tracking
> 240 distributed/disconnected subscribers using pull subscriptions running
> MSDE 2000
> We're having a high volume of suspect databases appearing. Causes are
varied
> but the majority are torn pages. None are due to running out of disk
space.
> Currently our fix is to drop the database & rebuild it using replication.
> However this is pretty slow. So alternatively, if we took daily backups of
> each subscriber db, would a simple restore make everything work again?
> If we take a backup at 1am, the user syncs at 9am and then subsequently
the
> database becomes suspect & we need to restore the backup. Will the
> subscriptions continue to work and will the data that was sync'd at 9am be
> retransmitted down to the subscriber?
> Does anyone foresee any other problems?
> thanks,
> Jonathan
>
|||Thanks for your help Hilary. Fixing the disk problem is harder than you'd
think as these torn pages are happening on 6 different models of IBM
Thinkpads and it is only happening in Production world. In over 1.5 years
we've yet to get a single one in development or test environments.
"Hilary Cotter" wrote:
> I think you should address your torn pages problem. For instance it could be
> a disk, controller, or a power purity problem.
> To address your question. If you restore the backup to the subscriber, the
> publisher will fill in the missing data since 9:00 am.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jonathan Ainsworth" <JonathanAinsworth@.discussions.microsoft.com> wrote in
> message news:55C49FD8-2BBC-406E-9815-0EFAD17268B6@.microsoft.com...
> column
> varied
> space.
> the
>
>
Showing posts with label sp3a. Show all posts
Showing posts with label sp3a. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
awe settings.
SQL2K Ent SP3a Win2k SP3
I've recently enabled awe memory on 2 of our servers.
Everything seems to be going OK, AWE memory counters
are active in performance monitor and the sql serror log
shows awe memory enabled on startup.
My question is, is the above information enough to know that
awe is working correctly? If not, what other things can be looked
at?
TIA.see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274750#6
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Stressed" <k@.c.co.uk> wrote in message
news:%23oI0KZzVDHA.2164@.TK2MSFTNGP09.phx.gbl...
> SQL2K Ent SP3a Win2k SP3
> I've recently enabled awe memory on 2 of our servers.
> Everything seems to be going OK, AWE memory counters
> are active in performance monitor and the sql serror log
> shows awe memory enabled on startup.
> My question is, is the above information enough to know that
> awe is working correctly? If not, what other things can be looked
> at?
> TIA.
>
>|||Also, when SQL Server starts you should see a line time in
the event log that says:
"Address Windowing Extensions enabled."
Ron
>--Original Message--
>see
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q274750#6
>--
>BR,
>Mark Broadbent mcdba,mcse+i
>_________________________
>"Stressed" <k@.c.co.uk> wrote in message
>news:%23oI0KZzVDHA.2164@.TK2MSFTNGP09.phx.gbl...
>> SQL2K Ent SP3a Win2k SP3
>> I've recently enabled awe memory on 2 of our servers.
>> Everything seems to be going OK, AWE memory counters
>> are active in performance monitor and the sql serror log
>> shows awe memory enabled on startup.
>> My question is, is the above information enough to know
that
>> awe is working correctly? If not, what other things can
be looked
>> at?
>> TIA.
>>
>>
>
>.
>
I've recently enabled awe memory on 2 of our servers.
Everything seems to be going OK, AWE memory counters
are active in performance monitor and the sql serror log
shows awe memory enabled on startup.
My question is, is the above information enough to know that
awe is working correctly? If not, what other things can be looked
at?
TIA.see
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274750#6
--
BR,
Mark Broadbent mcdba,mcse+i
_________________________
"Stressed" <k@.c.co.uk> wrote in message
news:%23oI0KZzVDHA.2164@.TK2MSFTNGP09.phx.gbl...
> SQL2K Ent SP3a Win2k SP3
> I've recently enabled awe memory on 2 of our servers.
> Everything seems to be going OK, AWE memory counters
> are active in performance monitor and the sql serror log
> shows awe memory enabled on startup.
> My question is, is the above information enough to know that
> awe is working correctly? If not, what other things can be looked
> at?
> TIA.
>
>|||Also, when SQL Server starts you should see a line time in
the event log that says:
"Address Windowing Extensions enabled."
Ron
>--Original Message--
>see
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q274750#6
>--
>BR,
>Mark Broadbent mcdba,mcse+i
>_________________________
>"Stressed" <k@.c.co.uk> wrote in message
>news:%23oI0KZzVDHA.2164@.TK2MSFTNGP09.phx.gbl...
>> SQL2K Ent SP3a Win2k SP3
>> I've recently enabled awe memory on 2 of our servers.
>> Everything seems to be going OK, AWE memory counters
>> are active in performance monitor and the sql serror log
>> shows awe memory enabled on startup.
>> My question is, is the above information enough to know
that
>> awe is working correctly? If not, what other things can
be looked
>> at?
>> TIA.
>>
>>
>
>.
>
Saturday, February 25, 2012
AWE and 3GB switches
I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?
If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
MG
"Terri" wrote:
> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>
|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...[vbcol=seagreen]
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
Server[vbcol=seagreen]
|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
MG
"Terri" wrote:
> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.com/sql_server_performance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> Server
>
>
|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>
|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> "you
> the
>
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?
If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
MG
"Terri" wrote:
> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>
|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...[vbcol=seagreen]
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
Server[vbcol=seagreen]
|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
MG
"Terri" wrote:
> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.com/sql_server_performance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> Server
>
>
|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>
|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> "you
> the
>
AWE and 3GB switches
I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
--
MG
"Terri" wrote:
> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
> > I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL
Server
> > 2000 Enterprise/SP3a. The server has 8GB of RAM
> >
> > I have set the /PAE switch in boot.ini and then ran the following:
> >
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> > GO
> > sp_configure 'awe enabled', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory', 6144
> > RECONFIGURE
> > GO
> >
> > I can confirm using the SQL Server:Memory Manager-Total Server Memory
> > counter that SQL Server is using 6303928 kbs of memory.
> >
> > Should I be using the 3GB switch? If so why?
> >
> >
> >
> >
> >
> >|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
--
MG
"Terri" wrote:
> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.com/sql_server_performance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> > If you want to use as much of the 8GB SQL server then no.
> > Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> >
> > Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> > RAM available.
> > --
> > MG
> >
> >
> > "Terri" wrote:
> >
> > > I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL
> Server
> > > 2000 Enterprise/SP3a. The server has 8GB of RAM
> > >
> > > I have set the /PAE switch in boot.ini and then ran the following:
> > >
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'awe enabled', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory', 6144
> > > RECONFIGURE
> > > GO
> > >
> > > I can confirm using the SQL Server:Memory Manager-Total Server Memory
> > > counter that SQL Server is using 6303928 kbs of memory.
> > >
> > > Should I be using the 3GB switch? If so why?
> > >
> > >
> > >
> > >
> > >
> > >
>
>|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
>> Hmmm...the wording is a little ambiguous. It does say in the article,
> "you
>> must enter one of the following switches in the boot line of the boot.ini
>> file..." and Perfmon is showing that more than 6GB on RAM is getting
>> used.
>> Here is a line that found in another article
>> http://support.microsoft.com/default.aspx/kb/274750 :
>> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
>> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
> the
>> /PAE switch SQL Server can only utilize up to 3 GB of memory."
>> It's doesn't say that you need both flags to use more that 4GB.
>
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
--
MG
"Terri" wrote:
> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.com/sql_server_performance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
> > I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL
Server
> > 2000 Enterprise/SP3a. The server has 8GB of RAM
> >
> > I have set the /PAE switch in boot.ini and then ran the following:
> >
> > sp_configure 'show advanced options', 1
> > RECONFIGURE
> > GO
> > sp_configure 'awe enabled', 1
> > RECONFIGURE
> > GO
> > sp_configure 'max server memory', 6144
> > RECONFIGURE
> > GO
> >
> > I can confirm using the SQL Server:Memory Manager-Total Server Memory
> > counter that SQL Server is using 6303928 kbs of memory.
> >
> > Should I be using the 3GB switch? If so why?
> >
> >
> >
> >
> >
> >|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
--
MG
"Terri" wrote:
> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.com/sql_server_performance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> > If you want to use as much of the 8GB SQL server then no.
> > Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> >
> > Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> > RAM available.
> > --
> > MG
> >
> >
> > "Terri" wrote:
> >
> > > I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL
> Server
> > > 2000 Enterprise/SP3a. The server has 8GB of RAM
> > >
> > > I have set the /PAE switch in boot.ini and then ran the following:
> > >
> > > sp_configure 'show advanced options', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'awe enabled', 1
> > > RECONFIGURE
> > > GO
> > > sp_configure 'max server memory', 6144
> > > RECONFIGURE
> > > GO
> > >
> > > I can confirm using the SQL Server:Memory Manager-Total Server Memory
> > > counter that SQL Server is using 6303928 kbs of memory.
> > >
> > > Should I be using the 3GB switch? If so why?
> > >
> > >
> > >
> > >
> > >
> > >
>
>|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
>> Hmmm...the wording is a little ambiguous. It does say in the article,
> "you
>> must enter one of the following switches in the boot line of the boot.ini
>> file..." and Perfmon is showing that more than 6GB on RAM is getting
>> used.
>> Here is a line that found in another article
>> http://support.microsoft.com/default.aspx/kb/274750 :
>> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
>> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
> the
>> /PAE switch SQL Server can only utilize up to 3 GB of memory."
>> It's doesn't say that you need both flags to use more that 4GB.
>
AWE and 3GB switches
I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Server
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
--
MG
"Terri" wrote:
> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Serv
er
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.c...ance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...[vbcol=seagreen]
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
>
Server[vbcol=seagreen]|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
--
MG
"Terri" wrote:
> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.c...ance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> Server
>
>|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> "you
> the
>
2000 Enterprise/SP3a. The server has 8GB of RAM
I have set the /PAE switch in boot.ini and then ran the following:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
I can confirm using the SQL Server:Memory Manager-Total Server Memory
counter that SQL Server is using 6303928 kbs of memory.
Should I be using the 3GB switch? If so why?If you want to use as much of the 8GB SQL server then no.
Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
RAM available.
--
MG
"Terri" wrote:
> I'm configuring a new server with Windows 2003 Enterprise SP1 and SQL Serv
er
> 2000 Enterprise/SP3a. The server has 8GB of RAM
> I have set the /PAE switch in boot.ini and then ran the following:
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> I can confirm using the SQL Server:Memory Manager-Total Server Memory
> counter that SQL Server is using 6303928 kbs of memory.
> Should I be using the 3GB switch? If so why?
>
>
>|||Hurme, thanks for your response. My confusion on this issue unfortunately
persists. Subsequent to my post I found the following article which says I
should use both, /3GB and /PAE.
http://www.sql-server-performance.c...ance_audit5.asp
"Hurme" <michael.geles@.thomson.com> wrote in message
news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...[vbcol=seagreen]
> If you want to use as much of the 8GB SQL server then no.
> Using the /3G switch will allow only up to 3GB of RAM to SQL Server.
> Likewise, you should leave approx. 1GB of RAM for the OS for every 8GB of
> RAM available.
> --
> MG
>
> "Terri" wrote:
>
Server[vbcol=seagreen]|||Hmmm...the wording is a little ambiguous. It does say in the article, "you
must enter one of the following switches in the boot line of the boot.ini
file..." and Perfmon is showing that more than 6GB on RAM is getting used.
Here is a line that found in another article
http://support.microsoft.com/default.aspx/kb/274750 :
"Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the
/PAE switch SQL Server can only utilize up to 3 GB of memory."
It's doesn't say that you need both flags to use more that 4GB.
HTH
--
MG
"Terri" wrote:
> Hurme, thanks for your response. My confusion on this issue unfortunately
> persists. Subsequent to my post I found the following article which says I
> should use both, /3GB and /PAE.
> http://www.sql-server-performance.c...ance_audit5.asp
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:93A66D75-882B-4501-9607-309CC354FA40@.microsoft.com...
> Server
>
>|||Yes, there is some ambiguity. Perhaps others will chime in with additional
thoughts.
"Hurme" <michael.geles@.thomson.com> wrote in message
news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> Hmmm...the wording is a little ambiguous. It does say in the article,
"you
> must enter one of the following switches in the boot line of the boot.ini
> file..." and Perfmon is showing that more than 6GB on RAM is getting used.
> Here is a line that found in another article
> http://support.microsoft.com/default.aspx/kb/274750 :
> "Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL
> Server allows SQL Server 2000 to utilize more than 4 GB memory. Without
the
> /PAE switch SQL Server can only utilize up to 3 GB of memory."
> It's doesn't say that you need both flags to use more that 4GB.
>|||Terry
http://support.microsoft.com/kb/283037/en-us
With more than 4GB, then you should use PAE, whether or not Win2K3 enables
this for you or not, use the registry settings to determine if it was
detected, but AWE will require PAE to make use of any address space above
4GB. In fact, the OS and SS will not even recognize memory greater than 4GB
even exists without PAE enabled.
"Terri" <terri@.cybernets.com> wrote in message
news:f0lsmn$g2k$1@.reader2.nmix.net...
> Yes, there is some ambiguity. Perhaps others will chime in with additional
> thoughts.
> "Hurme" <michael.geles@.thomson.com> wrote in message
> news:1A5C69C9-0D56-4F33-845C-457C11FAEE62@.microsoft.com...
> "you
> the
>
AWE
Our current production environment consists of an 2 node (active-active) SQL
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
but is performing well without it.
Thanks,
Jay
Hi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay
|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J
|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay
|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:
> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access it.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...[vbcol=seagreen]
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
but is performing well without it.
Thanks,
Jay
Hi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay
|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J
|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay
|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:
> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access it.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...[vbcol=seagreen]
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
AWE
Our current production environment consists of an 2 node (active-active) SQL
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
but is performing well without it.
Thanks,
JayHi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
--
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:
> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access it.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> > Our current production environment consists of an 2 node (active-active)
> > SQL
> > Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> > reenable Address Windowing Extensions (AWE). Is it recommended that I
> > turn
> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> > am
> > asking this because it appears to be performing well with AWE off. Also
> > should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> > now,
> > but is performing well without it.
> >
> > Thanks,
> > Jay
>
>|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
--
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
>> Just because you have lots of ram does not mean sql server will be able
>> to
>> use it all effectively. It depends a lot on your data and how you access
>> it.
>> If your cache hit ratio and page life expectancy arealways high and your
>> response times are good you may not need to turn it on. But if you do
>> you
>> certainly should not set it to 7gb since you have an active - active
>> configuration. Since AWE memory is not dynamic you can have a situation
>> where one instance gets 7GB and leaves only 1GB for the other instance
>> and
>> the OS and performance will suffer on all fronts.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "jay d" <jayd@.discussions.microsoft.com> wrote in message
>> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>> > Our current production environment consists of an 2 node
>> > (active-active)
>> > SQL
>> > Server 2000 SP3a database cluster. It was recently rebuilt and we did
>> > not
>> > reenable Address Windowing Extensions (AWE). Is it recommended that I
>> > turn
>> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM.
>> > I
>> > am
>> > asking this because it appears to be performing well with AWE off.
>> > Also
>> > should I allocate 7 GB of RAM to for 'max server memory'? It is not
>> > set
>> > now,
>> > but is performing well without it.
>> >
>> > Thanks,
>> > Jay
>>|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> > Our current production environment consists of an 2 node (active-active)
> > SQL
> > Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> > reenable Address Windowing Extensions (AWE). Is it recommended that I
> > turn
> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> > am
> > asking this because it appears to be performing well with AWE off. Also
> > should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> > now,
> > but is performing well without it.
> >
> > Thanks,
> > Jay
>
>|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> > Our current production environment consists of an 2 node (active-active)
> > SQL
> > Server 2000 SP3a database cluster. It was recently rebuilt and we did
> > not
> > reenable Address Windowing Extensions (AWE). Is it recommended that I
> > turn
> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> > am
> > asking this because it appears to be performing well with AWE off. Also
> > should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> > now,
> > but is performing well without it.
> >
> > Thanks,
> > Jay
>
>
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
but is performing well without it.
Thanks,
JayHi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set now,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
--
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:
> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access it.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> > Our current production environment consists of an 2 node (active-active)
> > SQL
> > Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> > reenable Address Windowing Extensions (AWE). Is it recommended that I
> > turn
> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> > am
> > asking this because it appears to be performing well with AWE off. Also
> > should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> > now,
> > but is performing well without it.
> >
> > Thanks,
> > Jay
>
>|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
--
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
>> Just because you have lots of ram does not mean sql server will be able
>> to
>> use it all effectively. It depends a lot on your data and how you access
>> it.
>> If your cache hit ratio and page life expectancy arealways high and your
>> response times are good you may not need to turn it on. But if you do
>> you
>> certainly should not set it to 7gb since you have an active - active
>> configuration. Since AWE memory is not dynamic you can have a situation
>> where one instance gets 7GB and leaves only 1GB for the other instance
>> and
>> the OS and performance will suffer on all fronts.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "jay d" <jayd@.discussions.microsoft.com> wrote in message
>> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>> > Our current production environment consists of an 2 node
>> > (active-active)
>> > SQL
>> > Server 2000 SP3a database cluster. It was recently rebuilt and we did
>> > not
>> > reenable Address Windowing Extensions (AWE). Is it recommended that I
>> > turn
>> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM.
>> > I
>> > am
>> > asking this because it appears to be performing well with AWE off.
>> > Also
>> > should I allocate 7 GB of RAM to for 'max server memory'? It is not
>> > set
>> > now,
>> > but is performing well without it.
>> >
>> > Thanks,
>> > Jay
>>|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> > Our current production environment consists of an 2 node (active-active)
> > SQL
> > Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> > reenable Address Windowing Extensions (AWE). Is it recommended that I
> > turn
> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> > am
> > asking this because it appears to be performing well with AWE off. Also
> > should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> > now,
> > but is performing well without it.
> >
> > Thanks,
> > Jay
>
>|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> > Our current production environment consists of an 2 node (active-active)
> > SQL
> > Server 2000 SP3a database cluster. It was recently rebuilt and we did
> > not
> > reenable Address Windowing Extensions (AWE). Is it recommended that I
> > turn
> > it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> > am
> > asking this because it appears to be performing well with AWE off. Also
> > should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> > now,
> > but is performing well without it.
> >
> > Thanks,
> > Jay
>
>
AWE
Our current production environment consists of an 2 node (active-active) SQL
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now
,
but is performing well without it.
Thanks,
JayHi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) S
QL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I tur
n
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I a
m
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set n
ow,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:
> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access i
t.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...[vbcol=seagreen]
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
>|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be fr
om
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
Server 2000 SP3a database cluster. It was recently rebuilt and we did not
reenable Address Windowing Extensions (AWE). Is it recommended that I turn
it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I am
asking this because it appears to be performing well with AWE off. Also
should I allocate 7 GB of RAM to for 'max server memory'? It is not set now
,
but is performing well without it.
Thanks,
JayHi,
If you have 8 GB ram please enable AWE. My value for SQL Server will be from
6 to 6.5 GB.
I will leave the rest to OS and other applications.
Thanks
Hari
SQL Server MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||"jay d" wrote:
> Our current production environment consists of an 2 node (active-active) S
QL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I tur
n
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I a
m
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set n
ow,
> but is performing well without it.
Jay,
If the servers are running well without AWE, I would say keep it turned
off.
In my experience, the AWE implementation has some bugs. Better to keep
it simple unless you need the extra memory.
-- J|||Just because you have lots of ram does not mean sql server will be able to
use it all effectively. It depends a lot on your data and how you access it.
If your cache hit ratio and page life expectancy arealways high and your
response times are good you may not need to turn it on. But if you do you
certainly should not set it to 7gb since you have an active - active
configuration. Since AWE memory is not dynamic you can have a situation
where one instance gets 7GB and leaves only 1GB for the other instance and
the OS and performance will suffer on all fronts.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
> Our current production environment consists of an 2 node (active-active)
> SQL
> Server 2000 SP3a database cluster. It was recently rebuilt and we did not
> reenable Address Windowing Extensions (AWE). Is it recommended that I
> turn
> it on? Both servers have 8 procs running at 2.8 Ghz and 8 GB of RAM. I
> am
> asking this because it appears to be performing well with AWE off. Also
> should I allocate 7 GB of RAM to for 'max server memory'? It is not set
> now,
> but is performing well without it.
> Thanks,
> Jay|||Thank you all for the help. One more quick question. If I am 3 instances
should I set the max server memory to 7 GIG on each of them or do I divide
the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
and Instance 3 - 2 GIG.
Thanks,
Jay
"Andrew J. Kelly" wrote:
> Just because you have lots of ram does not mean sql server will be able to
> use it all effectively. It depends a lot on your data and how you access i
t.
> If your cache hit ratio and page life expectancy arealways high and your
> response times are good you may not need to turn it on. But if you do you
> certainly should not set it to 7gb since you have an active - active
> configuration. Since AWE memory is not dynamic you can have a situation
> where one instance gets 7GB and leaves only 1GB for the other instance and
> the OS and performance will suffer on all fronts.
> --
> Andrew J. Kelly SQL MVP
>
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>|||As I stated when you set AWE it is no longer dynamic. The first instance to
come up will grab all the memory it can up to the MAX Memory setting. If
you set it to 7GB for each instance and you only have 8GB total there isn't
enough to go around. When you use AWE you can not share the memory. So if
you really do have three instances that is probably the real reason why AWE
is turned off to begin with.
Andrew J. Kelly SQL MVP
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:9144E391-55F4-4B7F-9858-321A6973AE50@.microsoft.com...[vbcol=seagreen]
> Thank you all for the help. One more quick question. If I am 3 instances
> should I set the max server memory to 7 GIG on each of them or do I divide
> the RAM among the 3 instances? ex Instance 1 - 3 GIG, Instance 2 - 3 GIG,
> and Instance 3 - 2 GIG.
> Thanks,
> Jay
> "Andrew J. Kelly" wrote:
>|||Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be fr
om
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>|||Just be sure not to apply SP4 for SQL Server. There is a bug with respect
to AWE. They are working on a fix.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"jay d" <jayd@.discussions.microsoft.com> wrote in message
news:8A900118-4F9B-4378-87F5-15EA79CC42F4@.microsoft.com...
Hi,
Thanks, I will enable AWE. If I have 2 instances of SQL running, should I
set each instance to 3 GIG RAM and then leave 2 GIG for the OS, just in case
one node of the cluster fails and both instances end up on the same node for
awhile?
Thanks,
Jay
"Hari Prasad" wrote:
> Hi,
> If you have 8 GB ram please enable AWE. My value for SQL Server will be
> from
> 6 to 6.5 GB.
> I will leave the rest to OS and other applications.
> Thanks
> Hari
> SQL Server MVP
> "jay d" <jayd@.discussions.microsoft.com> wrote in message
> news:2B3700C8-036C-454E-A50A-D156CBA98EAC@.microsoft.com...
>
>
Sunday, February 12, 2012
AutoShrink Database Transaction Log
I am using SQL Server 2000 Enterprise Edition with SP3A.
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
DanShrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx
.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/defaul...8&Product=sql2k
http://support.microsoft.com/defaul...kb;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/defaul...5&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx
.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
DanShrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx
.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/defaul...8&Product=sql2k
http://support.microsoft.com/defaul...kb;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/defaul...5&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx
.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
AutoShrink Database Transaction Log
I am using SQL Server 2000 Enterprise Edition with SP3A.
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
Dan
Shrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...b;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/default...&Product=sql2k
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
Dan
Shrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...b;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/default...&Product=sql2k
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
AutoShrink Database Transaction Log
I am using SQL Server 2000 Enterprise Edition with SP3A.
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
DanShrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/default.aspx?scid=kb;en-us;317375&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
Is there away that can I autoshrink the database
transaction log and not the database? Is there a command
I can run periodically to shrink the transaction log file?
Is there away I can tell which process caused the database
transaction log to grow so large?
Thank You,
DanShrink implies actually reducing the amount of space used by the actual OS
file. Shrink does not mean freeing up space within the transaction log
itself. You probably understand this, but I wanted to make the distinction
clear since generally speaking... you shoud NEVER autoshrink an important
production database. Why? Shrinking a file can create any number of
bottlenecks. Shink operations in large systems should be planned operations
in a managed production enviornment. And... Since you're running SQL EE...
you're probably running something very big. Otherwise you paid 4X more for
your license than you should have paid...
There is not a check box setting to autoshrink the log and not the database.
However, it would be easy to create a job to do this periodically that
issues dbcc shrinkfile. Again, I wouldn't recommend this...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan|||Here are a couple of good KB articles that should provided you with the
information necessary to shrink your tranlog
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
As far as knowing what application is causing your tranlog to grow becomes a
little more difficult. You really need to know what applications you have
running against your database and how they are commiting transactions. Here
is an article that might help you understand why the transaction log grows.
Hopefully this will give you some ideas on what it is that is causing your
problems.
http://support.microsoft.com/default.aspx?scid=kb;en-us;317375&Product=sql2k
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:129a301c4431f$a28949d0$a401280a@.phx.gbl...
> I am using SQL Server 2000 Enterprise Edition with SP3A.
> Is there away that can I autoshrink the database
> transaction log and not the database? Is there a command
> I can run periodically to shrink the transaction log file?
> Is there away I can tell which process caused the database
> transaction log to grow so large?
> Thank You,
> Dan
Subscribe to:
Posts (Atom)