Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>sql

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
Kay
Are you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.
|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegr oups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

Backing up secondary DB in log shipping ...

Hello can someone clear this up for me?
In BOL "Log Shipping Tips and Best Practices" is the following
recommendation:
"Generating Database Backups from the Secondary
Since the log shipped database is only a small increment of time out
of sync with the primary database, it is a good idea to use it to
generate the full database backups. This would keep the primary
production server free of the potential overhead and contention
incurred by a daily (or scheduled) backup. "
In my experience and from searching other threads in this group this
is actually not true - backing up the log shipped database will not
work, it will fail with the error:
"Database is in warm-standby state (set by executing RESTORE WITH
STANDBY) and cannot be backed up until the entire load sequence is
completed."
Am I missing something or is BOL just being misleading?
thanks,
PeterPeter,
http://support.microsoft.com/kb/311115 contains the correction.
Note: With SQL Server 2005, you it might be possible to create a snapshot
database over the secondary and back that up. (But I have no experience
with trying.)
RLF
<peter.wismer@.gmail.com> wrote in message
news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
> Hello can someone clear this up for me?
> In BOL "Log Shipping Tips and Best Practices" is the following
> recommendation:
> "Generating Database Backups from the Secondary
> Since the log shipped database is only a small increment of time out
> of sync with the primary database, it is a good idea to use it to
> generate the full database backups. This would keep the primary
> production server free of the potential overhead and contention
> incurred by a daily (or scheduled) backup. "
> In my experience and from searching other threads in this group this
> is actually not true - backing up the log shipped database will not
> work, it will fail with the error:
> "Database is in warm-standby state (set by executing RESTORE WITH
> STANDBY) and cannot be backed up until the entire load sequence is
> completed."
> Am I missing something or is BOL just being misleading?
> thanks,
> Peter
>|||On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> Peter,
> http://support.microsoft.com/kb/311115contains the correction.
> Note: With SQL Server 2005, you it might be possible to create a snapshot
> database over the secondary and back that up. (But I have no experience
> with trying.)
> RLF<peter.wis...@.gmail.com> wrote in message
> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>
> > Hello can someone clear this up for me?
> > In BOL "Log Shipping Tips and Best Practices" is the following
> > recommendation:
> > "Generating Database Backups from the Secondary
> > Since the log shipped database is only a small increment of time out
> > of sync with the primary database, it is a good idea to use it to
> > generate the full database backups. This would keep the primary
> > production server free of the potential overhead and contention
> > incurred by a daily (or scheduled) backup. "
> > In my experience and from searching other threads in this group this
> > is actually not true - backing up the log shipped database will not
> > work, it will fail with the error:
> > "Database is in warm-standby state (set by executing RESTORE WITH
> > STANDBY) and cannot be backed up until the entire load sequence is
> > completed."
> > Am I missing something or is BOL just being misleading?
> > thanks,
> > Peter- Hide quoted text -
> - Show quoted text -
Thanks very much Russell (I was beginning to mistrust my command of
the english language - thought I might need to find a Canadian
translation of BOL...)
Peter|||I think it was a case of wishes versus reality. - RLF
<peter.wismer@.gmail.com> wrote in message
news:1187288061.528821.290710@.j4g2000prf.googlegroups.com...
> On Aug 16, 1:12 pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
>> Peter,
>> http://support.microsoft.com/kb/311115contains the correction.
>> Note: With SQL Server 2005, you it might be possible to create a snapshot
>> database over the secondary and back that up. (But I have no experience
>> with trying.)
>> RLF<peter.wis...@.gmail.com> wrote in message
>> news:1187280994.613717.77730@.l22g2000prc.googlegroups.com...
>>
>> > Hello can someone clear this up for me?
>> > In BOL "Log Shipping Tips and Best Practices" is the following
>> > recommendation:
>> > "Generating Database Backups from the Secondary
>> > Since the log shipped database is only a small increment of time out
>> > of sync with the primary database, it is a good idea to use it to
>> > generate the full database backups. This would keep the primary
>> > production server free of the potential overhead and contention
>> > incurred by a daily (or scheduled) backup. "
>> > In my experience and from searching other threads in this group this
>> > is actually not true - backing up the log shipped database will not
>> > work, it will fail with the error:
>> > "Database is in warm-standby state (set by executing RESTORE WITH
>> > STANDBY) and cannot be backed up until the entire load sequence is
>> > completed."
>> > Am I missing something or is BOL just being misleading?
>> > thanks,
>> > Peter- Hide quoted text -
>> - Show quoted text -
> Thanks very much Russell (I was beginning to mistrust my command of
> the english language - thought I might need to find a Canadian
> translation of BOL...)
> Peter
>

Backing up Database

When I try to backup a SQL 2000 database I recieve the following error in
the output log. Any ideas? Thank you.
SQL 2000 w/ SP4
[39] Database SolRounder: Database Backup...
Destination: [G:\Backups\SolRounder\SolRounder_db_200704131221.BAK]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC SQL
Server Driver][SQL Server]Write on
'G:\Backups\SolRounder\SolRounder_db_200704131221.BAK' failed, status = 112.
See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
[40] Database SolRounderKids: Database Backup...
Destination:
[G:\Backups\SolRounderKids\SolRounderKids_db_200704131223.BAK]Gabe,
Error 112 is probably the Windows ERROR_DISK_FULL, which means that your G:
drive does not have enough space to hold your backup file.
See if you can clean up some things from the drive to give it more space.
RLF
"Gabe Matteson\" <gmatteson.rounder.com.nospam> wrote in message
news:Oz8yowefHHA.4552@.TK2MSFTNGP04.phx.gbl...
> When I try to backup a SQL 2000 database I recieve the following error in
> the output log. Any ideas? Thank you.
> SQL 2000 w/ SP4
> [39] Database SolRounder: Database Backup...
> Destination: [G:\Backups\SolRounder\SolRounder_db_200704131221.BAK]
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Write on
> 'G:\Backups\SolRounder\SolRounder_db_200704131221.BAK' failed, status => 112. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> [40] Database SolRounderKids: Database Backup...
> Destination:
> [G:\Backups\SolRounderKids\SolRounderKids_db_200704131223.BAK]
>|||Working now. Thank you Russell!
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ueOlD4efHHA.4552@.TK2MSFTNGP04.phx.gbl...
> Gabe,
> Error 112 is probably the Windows ERROR_DISK_FULL, which means that your
> G: drive does not have enough space to hold your backup file.
> See if you can clean up some things from the drive to give it more space.
> RLF
> "Gabe Matteson\" <gmatteson.rounder.com.nospam> wrote in message
> news:Oz8yowefHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> When I try to backup a SQL 2000 database I recieve the following error in
>> the output log. Any ideas? Thank you.
>> SQL 2000 w/ SP4
>> [39] Database SolRounder: Database Backup...
>> Destination: [G:\Backups\SolRounder\SolRounder_db_200704131221.BAK]
>> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3202: [Microsoft][ODBC
>> SQL Server Driver][SQL Server]Write on
>> 'G:\Backups\SolRounder\SolRounder_db_200704131221.BAK' failed, status =>> 112. See the SQL Server error log for more details.
>> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
>> terminating abnormally.
>> [40] Database SolRounderKids: Database Backup...
>> Destination:
>> [G:\Backups\SolRounderKids\SolRounderKids_db_200704131223.BAK]
>

Background expression

Hi All,

I tried using the following Expression in a field's background property:

=iif( Fields!ProjectedDate.Value between (getdate()+1) and (getdate()+7), Orange ,White )

And then I got this message, anyone got any ideas?

The background color expression for the textbox 'textbox49' contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

=iif( Fields!ProjectedDate.Value < (getdate()+1) AND Fields!ProjectedDate.Value > (getdate()+7), Orange ,White )

I dont think you can use between in expression.

sql

Back up of Encryption Keys and changing Windows Service Identity

I need to change windows server identity for the report service but I get
following error for this operation
ReportServicesConfigUI.WMIProvider.WMIProviderException: Key not valid for
use in specified state. (Exception from HRESULT: 0x8009000B)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.BackupEncryptionKey(Byte[]&
encryptedBytes, String password)
What is a solution for this?
Thanks,
ShimonOn Dec 10, 8:11 pm, "Shimon Sim" <shimonsim...@.community.nospam>
wrote:
> I need to change windows server identity for the report service but I get
> following error for this operation
> ReportServicesConfigUI.WMIProvider.WMIProviderException: Key not valid for
> use in specified state. (Exception from HRESULT: 0x8009000B)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.BackupEncryptionKey(Byte[]&
> encryptedBytes, String password)
> What is a solution for this?
> Thanks,
> Shimon
This link might be helpful.
http://www.developmentnow.com/g/115_2007_5_0_0_973220/Report-Server-Windows-Service-MSSQLSERVER.htm
Regards,
Enrique Martinez
Sr. Software Consultant

back log dbname with no_log

Hi,

I'm issuing the following statements to reduce the size of transaction log, My database is in simple recovery mode:

CHECKPOINT

GO

backup log TMDATABASE with truncate_only

go

dbcc shrinkfile (TMDATABASE_Log,2)

GO

The log file size still remains the same. Even though there are no active or open transactions...

Any insight greatly appreciated....

Thanks,

Razi

Perhaps the Log file is at either its defined size, or there is no free space.

These are useful sources:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

Also refer to Books Online, Topic: 'DBCC ShrinkFile'.

The File Does Not Shrink

If the shrink operation runs without error, but the file does not appear to have changed in size, verify that the file has adequate free space to remove by performing one of the following operations:

Run the following query.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

Run the DBCC SQLPERF command to return the space used in the transaction log.

|||

Hi,

Thanks for the reply but my log is still not shrinking. I tried using

"DBCC SQLPERF(logspace)" which shows Logsize = 499.875 and Log used = 0.04083052 0

This database in under compatibilty mode 80.

Also tried altering database file sizes using the GUI and ALTER DATABASE command but that didnt work since the initial size was larger.

There are no current connections to the database. Any insight?

Regards,

Razi

Back Issue

Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed, status =
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)

Back Issue

Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed, status = 1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed,
> status => 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)

Back Issue

Following is my log file for transaction log backup via DB maintainance plan
.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN
]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed, status
=
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)

AXSCPHST.DLL error in SQL Agent Error Log

I'm getting the following errors in my SQL Server Agent Error Logs:
[000] Unable to locate entry point for AXSCPHST.DLL in The specified module
could not be found (reason: ?)
[125] Subsystem 'ActiveScripting' could not be loaded (reason:
Initialization failed [subsystem code 0])
I'm running SQL Server 2000 SP4 on Windows Server 2003.
Any clue as to why these errors are appearing and how to eliminate them?
All my DTS tasks with scripts seem to be working inspite of the errors.
Scott
I am having the same problem, do you have a solution?
|||Check the path for the String Value in this registry key.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL
Server\80\Tools\ClientSetup]
"SQLPath"="C:\\Program Files\\Microsoft SQL Server\\80\\Tools"
Occasionally it will be missing the "s" at the end. Microsoft says that it
happens during the upgrade from MSDE to full SQL.

AXSCPHST.DLL error in SQL Agent Error Log

I'm getting the following errors in my SQL Server Agent Error Logs:
[000] Unable to locate entry point for AXSCPHST.DLL in The specified mod
ule
could not be found (reason: ')
[125] Subsystem 'ActiveScripting' could not be loaded (reason:
Initialization failed [subsystem code 0])
I'm running SQL Server 2000 SP4 on Windows Server 2003.
Any clue as to why these errors are appearing and how to eliminate them?
All my DTS tasks with scripts seem to be working inspite of the errors.
ScottI am having the same problem, do you have a solution?|||Check the path for the String Value in this registry key.
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL
Server\80\Tools\ClientSetup]
"SQLPath"="C:\\Program Files\\Microsoft SQL Server\\80\\Tools"
Occasionally it will be missing the "s" at the end. Microsoft says that it
happens during the upgrade from MSDE to full SQL.

Axis Object - Auto Interval Error

Hello,
I am trying to plot a graph using the following data expression:
=(sum(Fields!Closed.Value)*7.5)/sum(Fields!FTE.Value)
and I get an error whenever I try to publish the graph:
An error has occured while rendering chart chart1. Details: Axis Object - Auto Interval Error Axis Object - Auto Interval Error
Anyone know what causes this and how it can be fixed?
Thanks
Wow... found the answer. So an error in my data caused this expression to be infinity. The Auto Interval Error can not handle a data point that is infinite so to fix this simply perform a check. In this case:
=iif(sum(Fields!FTE.Value)=0,"100",((sum(Fields!Closed.Value)*7.5)/sum(Fields!FTE.Value)))
I used the value 100 here becasue it is beyond my scall so a value of 100 will be very obvious.

AWE Question

What would be the optimal max server memory setting for the following
machine?
Dedicated SQL Server:
Intel Xeon MP 3.3 GHz (4 physical Processors)
136 GB (C Drive)
8.0 GB RAM
Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
SQL Server Enterprise 2000, with SP3
The machine has following database sizes. All databases are being accessed
all the time by around 300+ concurrent users.
DB#1 - 536.44 MB
DB#2 - 1.09 GB
DB#3 - 3.27 GB
DB#4 - 4.92 GB
DB#5 - 5.29 GB
DB#6 - 15.09 GB
DB#7 - 50.13 GB
I am still learning the ins and outs of configuring higher end db servers.
Are there any other non-default server settings that I should change?"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:uqJkBN9uGHA.4756@.TK2MSFTNGP04.phx.gbl...
> What would be the optimal max server memory setting for the following
> machine?
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
> Microsoft Windows 2003 Enterprise Edition Service Pack 1 Build 3790
> SQL Server Enterprise 2000, with SP3
> The machine has following database sizes. All databases are being accessed
> all the time by around 300+ concurrent users.
> DB#1 - 536.44 MB
> DB#2 - 1.09 GB
> DB#3 - 3.27 GB
> DB#4 - 4.92 GB
> DB#5 - 5.29 GB
> DB#6 - 15.09 GB
> DB#7 - 50.13 GB
> I am still learning the ins and outs of configuring higher end db servers.
>
Does each user access all databases or does each database have its own set
of users?
If the different databsaes represent seperate applications, you should
consider dividing the server into multiple SQL instances to divide up the
memory on the box and optionally the CPU's into 2 or 3 different resource
pools. 32bit SQL Server works more efficiently when it doesn't have to use
AWE to access memory, and multiple instances allow you to easilly partition
memory and CPU resources between the workloads.
David|||>
> Does each user access all databases or does each database have its own set
> of users?
All databases are the same application (different clients on each db similar
to an ASP service).
Each user usually accesses only a single database. Admin users would need
access to all.
> If the different databsaes represent seperate applications, you should
> consider dividing the server into multiple SQL instances to divide up the
> memory on the box and optionally the CPU's into 2 or 3 different resource
> pools. 32bit SQL Server works more efficiently when it doesn't have to
> use AWE to access memory, and multiple instances allow you to easilly
> partition memory and CPU resources between the workloads.
>
Interesting option. Your making me think about that.
You mention that 32bit SQL Server works more efficiently when it doesn't use
AWE. Does it affect performance that much?
My concern is the 50 GB database itself would benefit from more then 2 GB of
RAM.
You also mention that you can configure CPUs into different resource pools.
How does this work? Do you have a knowledge base link?|||"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
> >
>> Does each user access all databases or does each database have its own
>> set of users?
> All databases are the same application (different clients on each db
> similar to an ASP service).
> Each user usually accesses only a single database. Admin users would need
> access to all.
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up the
>> memory on the box and optionally the CPU's into 2 or 3 different resource
>> pools. 32bit SQL Server works more efficiently when it doesn't have to
>> use AWE to access memory, and multiple instances allow you to easilly
>> partition memory and CPU resources between the workloads.
> Interesting option. Your making me think about that.
> You mention that 32bit SQL Server works more efficiently when it doesn't
> use AWE. Does it affect performance that much?
> My concern is the 50 GB database itself would benefit from more then 2 GB
> of RAM.
Yes it would, but you have 7 databses and only 8G of ram. How much ram can
you afford to allocate for that one workload. And using the /3GB switch you
can give a SQL instance access to 3GB of memory without using AWE.
Also you will have one procedure cache per instance, and on 32bit SQL is
strictly limited in size. Multiple instances would have multiple procedure
caches.
"On 32-bit platforms configured with 4 GB or more of physical memory, you
can either use Address Windowing Extensions (AWE) or use multiple instances
of SQL Server as a way to fully utilize the large amount of physical memory.
AWE may work well for some scenarios; however, you should be aware that AWE
memory can only be used for data cache. The memory for procedure cache,
connections, locks, and other internal resources of SQL Server must come
from the 2 GB (or 3GB, depending on the settings used) portion of the
virtual memory. On systems needing to support a large number of databases
and user connections, multiple instances of SQL Server may be a better
approach to fully alleviate the 2 GB or 3GB memory constraint imposed by the
32-bit platform for these data structures."
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
> You also mention that you can configure CPUs into different resource
> pools. How does this work? Do you have a knowledge base link?
>
Simple partition of CPU's to SQL Instances should be done with the "affinity
mask" server option.
SQL Server 2005 Books Online
affinity mask Option
http://msdn2.microsoft.com/en-us/library/ms187104.aspx
Dynamic CPU allocation policies can be implemented with WSRM:
Windows System Resource Manager
http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would need
>> access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it doesn't
>> have to use AWE to access memory, and multiple instances allow you to
>> easilly partition memory and CPU resources between the workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2 GB
>> of RAM.
> Yes it would, but you have 7 databses and only 8G of ram. How much ram
> can you afford to allocate for that one workload. And using the /3GB
> switch you can give a SQL instance access to 3GB of memory without using
> AWE.
> Also you will have one procedure cache per instance, and on 32bit SQL is
> strictly limited in size. Multiple instances would have multiple
> procedure caches.
> "On 32-bit platforms configured with 4 GB or more of physical memory, you
> can either use Address Windowing Extensions (AWE) or use multiple
> instances of SQL Server as a way to fully utilize the large amount of
> physical memory. AWE may work well for some scenarios; however, you should
> be aware that AWE memory can only be used for data cache. The memory for
> procedure cache, connections, locks, and other internal resources of SQL
> Server must come from the 2 GB (or 3GB, depending on the settings used)
> portion of the virtual memory. On systems needing to support a large
> number of databases and user connections, multiple instances of SQL Server
> may be a better approach to fully alleviate the 2 GB or 3GB memory
> constraint imposed by the 32-bit platform for these data structures."
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
> Simple partition of CPU's to SQL Instances should be done with the
> "affinity mask" server option.
> SQL Server 2005 Books Online
> affinity mask Option
> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>
> Dynamic CPU allocation policies can be implemented with WSRM:
> Windows System Resource Manager
> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
> David
>
Thanks David. This info is very helpful.|||Isaac
> Dedicated SQL Server:
> Intel Xeon MP 3.3 GHz (4 physical Processors)
> 136 GB (C Drive)
> 8.0 GB RAM
I'd consider (based on your description) MAX Memory 7GB
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:%23$AxxK%23uGHA.4752@.TK2MSFTNGP02.phx.gbl...
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:O$v%232B%23uGHA.3428@.TK2MSFTNGP02.phx.gbl...
>> "Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
>> news:%23MUPzp9uGHA.4160@.TK2MSFTNGP06.phx.gbl...
>> >
>> Does each user access all databases or does each database have its own
>> set of users?
>> All databases are the same application (different clients on each db
>> similar to an ASP service).
>> Each user usually accesses only a single database. Admin users would
>> need access to all.
>>
>> If the different databsaes represent seperate applications, you should
>> consider dividing the server into multiple SQL instances to divide up
>> the memory on the box and optionally the CPU's into 2 or 3 different
>> resource pools. 32bit SQL Server works more efficiently when it
>> doesn't have to use AWE to access memory, and multiple instances allow
>> you to easilly partition memory and CPU resources between the
>> workloads.
>>
>> Interesting option. Your making me think about that.
>> You mention that 32bit SQL Server works more efficiently when it doesn't
>> use AWE. Does it affect performance that much?
>> My concern is the 50 GB database itself would benefit from more then 2
>> GB of RAM.
>> Yes it would, but you have 7 databses and only 8G of ram. How much ram
>> can you afford to allocate for that one workload. And using the /3GB
>> switch you can give a SQL instance access to 3GB of memory without using
>> AWE.
>> Also you will have one procedure cache per instance, and on 32bit SQL is
>> strictly limited in size. Multiple instances would have multiple
>> procedure caches.
>> "On 32-bit platforms configured with 4 GB or more of physical memory, you
>> can either use Address Windowing Extensions (AWE) or use multiple
>> instances of SQL Server as a way to fully utilize the large amount of
>> physical memory. AWE may work well for some scenarios; however, you
>> should be aware that AWE memory can only be used for data cache. The
>> memory for procedure cache, connections, locks, and other internal
>> resources of SQL Server must come from the 2 GB (or 3GB, depending on the
>> settings used) portion of the virtual memory. On systems needing to
>> support a large number of databases and user connections, multiple
>> instances of SQL Server may be a better approach to fully alleviate the 2
>> GB or 3GB memory constraint imposed by the 32-bit platform for these data
>> structures."
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/32bitconsolidation.mspx
>> You also mention that you can configure CPUs into different resource
>> pools. How does this work? Do you have a knowledge base link?
>>
>> Simple partition of CPU's to SQL Instances should be done with the
>> "affinity mask" server option.
>> SQL Server 2005 Books Online
>> affinity mask Option
>> http://msdn2.microsoft.com/en-us/library/ms187104.aspx
>>
>> Dynamic CPU allocation policies can be implemented with WSRM:
>> Windows System Resource Manager
>> http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
>> David
> Thanks David. This info is very helpful.
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uYF1sPEvGHA.724@.TK2MSFTNGP05.phx.gbl...
> Isaac
>> Dedicated SQL Server:
>> Intel Xeon MP 3.3 GHz (4 physical Processors)
>> 136 GB (C Drive)
>> 8.0 GB RAM
>
> I'd consider (based on your description) MAX Memory 7GB
>
Thanks Uri.

AWE on Clustering

Thank you for Geoff N. Hiten provides me very useful info.
Please experts confirm with the following.
Please take alook at the follwing list and tell me whether it looks good to
you.
SQL Server 2000 (SP4)+ Windows 2003 Clustering. Two nodes , on each node
there is ONE SQL instance.
a) SQL node1 --(Active) has 8GB
b) SQL node1 --(Passive) has 8GB
c) Boot.Ini has \3GB\PAE on both servers of cluster
1) Install hotfix from(http://support.microsoft.com/kb/899761) on SQL1 node
(Active)
2) Install hotfix from(http://support.microsoft.com/kb/899761) on SQL2 node
(Passive)
3) Grant 'lock pages in memory' on on SQL1 node (Active)
4) Grant 'lock pages in memory' on on SQL1 node (Passive)
5) Enabling AWE on SQL1 node (Active)--set to 6GB
6) Enabling AWE on SQL1 node (Passive)--set to 6GB
Thanks you again
Hotfixes are installed to an instance, not a node. You start the install
fromthe console that owns the instance, but the binaries are loaded on all
nodes. Otherwise, the pln looks good.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alex" <test@.hotmail.com> wrote in message
news:OqToVtTlHHA.4628@.TK2MSFTNGP06.phx.gbl...
> Thank you for Geoff N. Hiten provides me very useful info.
> Please experts confirm with the following.
> Please take alook at the follwing list and tell me whether it looks good
> to you.
> SQL Server 2000 (SP4)+ Windows 2003 Clustering. Two nodes , on each node
> there is ONE SQL instance.
> a) SQL node1 --(Active) has 8GB
> b) SQL node1 --(Passive) has 8GB
> c) Boot.Ini has \3GB\PAE on both servers of cluster
>
> 1) Install hotfix from(http://support.microsoft.com/kb/899761) on SQL1
> node
> (Active)
> 2) Install hotfix from(http://support.microsoft.com/kb/899761) on SQL2
> node
> (Passive)
> 3) Grant 'lock pages in memory' on on SQL1 node (Active)
> 4) Grant 'lock pages in memory' on on SQL1 node (Passive)
> 5) Enabling AWE on SQL1 node (Active)--set to 6GB
> 6) Enabling AWE on SQL1 node (Passive)--set to 6GB
> Thanks you again
>
|||Thank you sir.
> Hotfixes are installed to an instance, not a node.
Yes , I meant on node that hostes an instance. Sure on instance.
1) Install hotfix from(http://support.microsoft.com/kb/899761) on Instance
of SQL1 node
(Active)
2) Install hotfix from(http://support.microsoft.com/kb/899761) on Instance
SQL2 node
Being a little bit confused, should i install a hotfix on Passive also?
Thank you again
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23vz$JEclHHA.3496@.TK2MSFTNGP03.phx.gbl...
> Hotfixes are installed to an instance, not a node. You start the install
> fromthe console that owns the instance, but the binaries are loaded on all
> nodes. Otherwise, the pln looks good.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Alex" <test@.hotmail.com> wrote in message
> news:OqToVtTlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>
|||And ofcause I meant
3) Grant 'lock pages in memory' on on SQL1 node (Active)
4) Grant 'lock pages in memory' on on SQL2 node (Passive)
5) Enabling AWE on SQL1 node (Active)--set to 6GB
6) Enabling AWE on SQL2 node (Passive)--set to 6GB
Changed from SQL1 node to SQL2 node
"Alex" <test@.hotmail.com> wrote in message
news:%23vDPQSelHHA.1340@.TK2MSFTNGP04.phx.gbl...
> Thank you sir.
> Yes , I meant on node that hostes an instance. Sure on instance.
> 1) Install hotfix from(http://support.microsoft.com/kb/899761) on Instance
> of SQL1 node
> (Active)
> 2) Install hotfix from(http://support.microsoft.com/kb/899761) on Instance
> SQL2 node
> Being a little bit confused, should i install a hotfix on Passive also?
> Thank you again
>
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23vz$JEclHHA.3496@.TK2MSFTNGP03.phx.gbl...
>
|||A clarification Awe is "managed" by SQL Server so if you set up the fixed
memory from EM to 6 gb it will be the same on both nodes, there's no need or
way to put on each node.
Regards.

AWE Memory

I am having a problem with SQL using the memory that should be available to it.
I have the following configuration:
Hardware:
CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
MEMORY: 8 GB RAM
OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
SQL: SQL 2000 Enterprise
AWE is enabled
The user account for SQL has Lock Pages in Memory rights
My issue:
I have review the related threads on this site and compared the recommended
settings to my system. I have not found any differences. However the size of
the SQL working memory set will not increase beyond 1.7 GB.
I found a reference on MSDN that stated that when AWE is enabled an entry
"Address Windowing Extension Enabled" is added to the SQL Error Log. I find
no such entry in my log.
I seems as though AWE is not actually running even though all my setting
seem to be ok.
What have I missed?
Atvar
Atvar,
You also have to set the /3GB switch in boot.ini for SQL Server EE to use
the extra 1GB below the 4GB boundary. Even then it will report something
like 2.7GB used.
See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
http://support.microsoft.com/default...b;en-us;274750
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> I am having a problem with SQL using the memory that should be available
to it.
> I have the following configuration:
> Hardware:
> CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> MEMORY: 8 GB RAM
> OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> SQL: SQL 2000 Enterprise
> AWE is enabled
> The user account for SQL has Lock Pages in Memory rights
> My issue:
> I have review the related threads on this site and compared the
recommended
> settings to my system. I have not found any differences. However the size
of
> the SQL working memory set will not increase beyond 1.7 GB.
> I found a reference on MSDN that stated that when AWE is enabled an entry
> "Address Windowing Extension Enabled" is added to the SQL Error Log. I
find
> no such entry in my log.
> I seems as though AWE is not actually running even though all my setting
> seem to be ok.
> What have I missed?
> --
> Atvar
|||I thought the /3GB switch was not required and was just an option. Is this
not correct?
"Ron Talmage" wrote:

> Atvar,
> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
> the extra 1GB below the 4GB boundary. Even then it will report something
> like 2.7GB used.
> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
> http://support.microsoft.com/default...b;en-us;274750
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> to it.
> recommended
> of
> find
>
>
|||It is not required but a good idea in this case. It is not the reason you
are seeing what you say though. I would go back and start from scratch and
ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE turned
on and lock pages in memory for the right account. Verify each, don't just
assume.
Andrew J. Kelly SQL MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...[vbcol=seagreen]
>I thought the /3GB switch was not required and was just an option. Is this
> not correct?
>
> "Ron Talmage" wrote:
|||Thanks for the response.
I did review things as you suggested. The server is running WIn2003
Enterprise (not called advanded in 2003) and the PAE and AWE are set.
However it turn out that the SQL is standard and not Enterprise. This site
uses several SQL servers and the install was done by their IT department.
This server was specified for SQL Enterprise and the license was purchased.
It appears that the wrong license was installed by the IT department. I
think this is my issue as I understand that SQL standard is limited to 2 GB
of memory.
"Andrew J. Kelly" wrote:

> It is not required but a good idea in this case. It is not the reason you
> are seeing what you say though. I would go back and start from scratch and
> ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE turned
> on and lock pages in memory for the right account. Verify each, don't just
> assume.
> --
> Andrew J. Kelly SQL MVP
>
> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
>
>
|||Yes that is it. Standard SQL will only use 2GB regardless of how much the
server has.
Andrew J. Kelly SQL MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:433230CD-B250-4CB0-AEAE-9ED0A3F954C8@.microsoft.com...[vbcol=seagreen]
> Thanks for the response.
> I did review things as you suggested. The server is running WIn2003
> Enterprise (not called advanded in 2003) and the PAE and AWE are set.
> However it turn out that the SQL is standard and not Enterprise. This
> site
> uses several SQL servers and the install was done by their IT department.
> This server was specified for SQL Enterprise and the license was
> purchased.
> It appears that the wrong license was installed by the IT department. I
> think this is my issue as I understand that SQL standard is limited to 2
> GB
> of memory.
>
> "Andrew J. Kelly" wrote:

AWE Memory

I am having a problem with SQL using the memory that should be available to it.
I have the following configuration:
Hardware:
CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
MEMORY: 8 GB RAM
OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
SQL: SQL 2000 Enterprise
AWE is enabled
The user account for SQL has Lock Pages in Memory rights
My issue:
I have review the related threads on this site and compared the recommended
settings to my system. I have not found any differences. However the size of
the SQL working memory set will not increase beyond 1.7 GB.
I found a reference on MSDN that stated that when AWE is enabled an entry
"Address Windowing Extension Enabled" is added to the SQL Error Log. I find
no such entry in my log.
I seems as though AWE is not actually running even though all my setting
seem to be ok.
What have I missed?
--
AtvarAtvar,
You also have to set the /3GB switch in boot.ini for SQL Server EE to use
the extra 1GB below the 4GB boundary. Even then it will report something
like 2.7GB used.
See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> I am having a problem with SQL using the memory that should be available
to it.
> I have the following configuration:
> Hardware:
> CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> MEMORY: 8 GB RAM
> OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> SQL: SQL 2000 Enterprise
> AWE is enabled
> The user account for SQL has Lock Pages in Memory rights
> My issue:
> I have review the related threads on this site and compared the
recommended
> settings to my system. I have not found any differences. However the size
of
> the SQL working memory set will not increase beyond 1.7 GB.
> I found a reference on MSDN that stated that when AWE is enabled an entry
> "Address Windowing Extension Enabled" is added to the SQL Error Log. I
find
> no such entry in my log.
> I seems as though AWE is not actually running even though all my setting
> seem to be ok.
> What have I missed?
> --
> Atvar|||I thought the /3GB switch was not required and was just an option. Is this
not correct?
"Ron Talmage" wrote:
> Atvar,
> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
> the extra 1GB below the 4GB boundary. Even then it will report something
> like 2.7GB used.
> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> > I am having a problem with SQL using the memory that should be available
> to it.
> >
> > I have the following configuration:
> >
> > Hardware:
> >
> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> > MEMORY: 8 GB RAM
> >
> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> >
> > SQL: SQL 2000 Enterprise
> >
> > AWE is enabled
> > The user account for SQL has Lock Pages in Memory rights
> >
> > My issue:
> >
> > I have review the related threads on this site and compared the
> recommended
> > settings to my system. I have not found any differences. However the size
> of
> > the SQL working memory set will not increase beyond 1.7 GB.
> >
> > I found a reference on MSDN that stated that when AWE is enabled an entry
> > "Address Windowing Extension Enabled" is added to the SQL Error Log. I
> find
> > no such entry in my log.
> >
> > I seems as though AWE is not actually running even though all my setting
> > seem to be ok.
> >
> > What have I missed?
> >
> > --
> > Atvar
>
>|||It is not required but a good idea in this case. It is not the reason you
are seeing what you say though. I would go back and start from scratch and
ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE turned
on and lock pages in memory for the right account. Verify each, don't just
assume.
--
Andrew J. Kelly SQL MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
>I thought the /3GB switch was not required and was just an option. Is this
> not correct?
>
> "Ron Talmage" wrote:
>> Atvar,
>> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
>> the extra 1GB below the 4GB boundary. Even then it will report something
>> like 2.7GB used.
>> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
>> Hope this helps,
>> Ron
>> --
>> Ron Talmage
>> SQL Server MVP
>> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
>> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
>> > I am having a problem with SQL using the memory that should be
>> > available
>> to it.
>> >
>> > I have the following configuration:
>> >
>> > Hardware:
>> >
>> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
>> > MEMORY: 8 GB RAM
>> >
>> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
>> >
>> > SQL: SQL 2000 Enterprise
>> >
>> > AWE is enabled
>> > The user account for SQL has Lock Pages in Memory rights
>> >
>> > My issue:
>> >
>> > I have review the related threads on this site and compared the
>> recommended
>> > settings to my system. I have not found any differences. However the
>> > size
>> of
>> > the SQL working memory set will not increase beyond 1.7 GB.
>> >
>> > I found a reference on MSDN that stated that when AWE is enabled an
>> > entry
>> > "Address Windowing Extension Enabled" is added to the SQL Error Log. I
>> find
>> > no such entry in my log.
>> >
>> > I seems as though AWE is not actually running even though all my
>> > setting
>> > seem to be ok.
>> >
>> > What have I missed?
>> >
>> > --
>> > Atvar
>>|||Thanks for the response.
I did review things as you suggested. The server is running WIn2003
Enterprise (not called advanded in 2003) and the PAE and AWE are set.
However it turn out that the SQL is standard and not Enterprise. This site
uses several SQL servers and the install was done by their IT department.
This server was specified for SQL Enterprise and the license was purchased.
It appears that the wrong license was installed by the IT department. I
think this is my issue as I understand that SQL standard is limited to 2 GB
of memory.
"Andrew J. Kelly" wrote:
> It is not required but a good idea in this case. It is not the reason you
> are seeing what you say though. I would go back and start from scratch and
> ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE turned
> on and lock pages in memory for the right account. Verify each, don't just
> assume.
> --
> Andrew J. Kelly SQL MVP
>
> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
> >I thought the /3GB switch was not required and was just an option. Is this
> > not correct?
> >
> >
> > "Ron Talmage" wrote:
> >
> >> Atvar,
> >>
> >> You also have to set the /3GB switch in boot.ini for SQL Server EE to use
> >> the extra 1GB below the 4GB boundary. Even then it will report something
> >> like 2.7GB used.
> >>
> >> See KB 274750, "How to configure memory for more than 2 GB in SQL Server"
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
> >>
> >> Hope this helps,
> >> Ron
> >> --
> >> Ron Talmage
> >> SQL Server MVP
> >>
> >> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
> >> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
> >> > I am having a problem with SQL using the memory that should be
> >> > available
> >> to it.
> >> >
> >> > I have the following configuration:
> >> >
> >> > Hardware:
> >> >
> >> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
> >> > MEMORY: 8 GB RAM
> >> >
> >> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
> >> >
> >> > SQL: SQL 2000 Enterprise
> >> >
> >> > AWE is enabled
> >> > The user account for SQL has Lock Pages in Memory rights
> >> >
> >> > My issue:
> >> >
> >> > I have review the related threads on this site and compared the
> >> recommended
> >> > settings to my system. I have not found any differences. However the
> >> > size
> >> of
> >> > the SQL working memory set will not increase beyond 1.7 GB.
> >> >
> >> > I found a reference on MSDN that stated that when AWE is enabled an
> >> > entry
> >> > "Address Windowing Extension Enabled" is added to the SQL Error Log. I
> >> find
> >> > no such entry in my log.
> >> >
> >> > I seems as though AWE is not actually running even though all my
> >> > setting
> >> > seem to be ok.
> >> >
> >> > What have I missed?
> >> >
> >> > --
> >> > Atvar
> >>
> >>
> >>
>
>|||Yes that is it. Standard SQL will only use 2GB regardless of how much the
server has.
--
Andrew J. Kelly SQL MVP
"Atvar" <Atvar@.discussions.microsoft.com> wrote in message
news:433230CD-B250-4CB0-AEAE-9ED0A3F954C8@.microsoft.com...
> Thanks for the response.
> I did review things as you suggested. The server is running WIn2003
> Enterprise (not called advanded in 2003) and the PAE and AWE are set.
> However it turn out that the SQL is standard and not Enterprise. This
> site
> uses several SQL servers and the install was done by their IT department.
> This server was specified for SQL Enterprise and the license was
> purchased.
> It appears that the wrong license was installed by the IT department. I
> think this is my issue as I understand that SQL standard is limited to 2
> GB
> of memory.
>
> "Andrew J. Kelly" wrote:
>> It is not required but a good idea in this case. It is not the reason
>> you
>> are seeing what you say though. I would go back and start from scratch
>> and
>> ensure you really have Enterprise SQL, WIn2003 Advanced, PAE and AWE
>> turned
>> on and lock pages in memory for the right account. Verify each, don't
>> just
>> assume.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
>> news:97FEA326-585E-460E-AE8D-6910CB4E9345@.microsoft.com...
>> >I thought the /3GB switch was not required and was just an option. Is
>> >this
>> > not correct?
>> >
>> >
>> > "Ron Talmage" wrote:
>> >
>> >> Atvar,
>> >>
>> >> You also have to set the /3GB switch in boot.ini for SQL Server EE to
>> >> use
>> >> the extra 1GB below the 4GB boundary. Even then it will report
>> >> something
>> >> like 2.7GB used.
>> >>
>> >> See KB 274750, "How to configure memory for more than 2 GB in SQL
>> >> Server"
>> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
>> >>
>> >> Hope this helps,
>> >> Ron
>> >> --
>> >> Ron Talmage
>> >> SQL Server MVP
>> >>
>> >> "Atvar" <Atvar@.discussions.microsoft.com> wrote in message
>> >> news:741D40E0-A2C4-4529-9F64-56041A96C704@.microsoft.com...
>> >> > I am having a problem with SQL using the memory that should be
>> >> > available
>> >> to it.
>> >> >
>> >> > I have the following configuration:
>> >> >
>> >> > Hardware:
>> >> >
>> >> > CPU: 4 XEON 2.0 GHZ (with HT) - acts like 8
>> >> > MEMORY: 8 GB RAM
>> >> >
>> >> > OS: Windows 2003 Advanced Server (PAE is enabled in boot.ini)
>> >> >
>> >> > SQL: SQL 2000 Enterprise
>> >> >
>> >> > AWE is enabled
>> >> > The user account for SQL has Lock Pages in Memory rights
>> >> >
>> >> > My issue:
>> >> >
>> >> > I have review the related threads on this site and compared the
>> >> recommended
>> >> > settings to my system. I have not found any differences. However the
>> >> > size
>> >> of
>> >> > the SQL working memory set will not increase beyond 1.7 GB.
>> >> >
>> >> > I found a reference on MSDN that stated that when AWE is enabled an
>> >> > entry
>> >> > "Address Windowing Extension Enabled" is added to the SQL Error Log.
>> >> > I
>> >> find
>> >> > no such entry in my log.
>> >> >
>> >> > I seems as though AWE is not actually running even though all my
>> >> > setting
>> >> > seem to be ok.
>> >> >
>> >> > What have I missed?
>> >> >
>> >> > --
>> >> > Atvar
>> >>
>> >>
>> >>
>>

AWE and SQL Cluster

Hi, I have the following questions regarding AWE on SQL 2000 with Windows
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
Sean
I would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean
|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/pro...y/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>
|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/downlo...dv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/pro...y/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
>
|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:[vbcol=seagreen]
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/pro...y/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/downlo...dv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>
|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegr oups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>
|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||That should have read 'above'.

> SQL Server will use memory above 2 GB for data cache only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>
|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>

AWE and SQL Cluster

Hi, I have the following questions regarding AWE on SQL 2000 with Windows
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
SeanI would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
> >I would definately suggest using the AWE enabled option set to true.
> > especially if each node is dedicated to sql server. you will have to
> > restart the sql services to have the changes take place. check the
> > BOL: awe enabled option and the section managing awe memory.
> >
> > Sean T Shen wrote:
> >> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
> >> Server
> >> instance running). The SA has upgraded the memory of both nodes to 8G
> >> each.
> >> And I am tempted to use AWE to boost my SQL Server performance. The
> >> server
> >> is dedicated to running SQL Server.
> >>
> >> After AWE is enabled, can I configure my SQL Server max memory to
> >> something
> >> like 7G?
> >> Concern is, while it may run OK during normal operating mode, what
> >> will
> >> happen in case both SQL Servers are moved to one node?
> >>
> >> Thanks very much in advance.
> >> Sean
> >|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and release
>> memory from the operating system as needed. Dynamic memory allows you to
>> set a maximum amount of physical memory that the operating system can
>> allocate to SQL Server. This allows the remaining memory to be reserved
>> for other purposes, such as the operating system and other SQL Server
>> instances. Fixed memory allows you to set a specific amount of physical
>> memory that SQL Server can use. This amount neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance from
>> another node. If a server does not use a lot of memory compared to the
>> available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By doing
>> so, you leave sufficient memory for the failover to occur quickly and
>> successfully. If an instance uses all of the available physical memory,
>> failover will occur more slowly and can fail if memory is not released
>> quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegroups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of
>> a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate
>> memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the
>> cluster, you must determine the total amount of physical memory required
>> for
>> each server and determine the appropriate allocation of memory for each
>> SQL
>> Server instance. Performance of SQL Server is directly related to the
>> amount
>> of physical memory allocated to it by the operating system. SQL Server
>> provides two modes for memory management: dynamic and fixed. Dynamic
>> memory
>> allocation lets SQL acquire and release memory from the operating system
>> as
>> needed. Dynamic memory allows you to set a maximum amount of physical
>> memory
>> that the operating system can allocate to SQL Server. This allows the
>> remaining memory to be reserved for other purposes, such as the operating
>> system and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory
>> is available to support the failover of a SQL Server instance from
>> another
>> node. If a server does not use a lot of memory compared to the available
>> memory on the server, specify dynamic memory and set the maximum amount
>> of
>> memory that the operating system can allocate. By doing so, you leave
>> sufficient memory for the failover to occur quickly and successfully. If
>> an
>> instance uses all of the available physical memory, failover will occur
>> more
>> slowly and can fail if memory is not released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>> >I would definately suggest using the AWE enabled option set to true.
>> > especially if each node is dedicated to sql server. you will have to
>> > restart the sql services to have the changes take place. check the
>> > BOL: awe enabled option and the section managing awe memory.
>> >
>> > Sean T Shen wrote:
>> >> Hi, I have the following questions regarding AWE on SQL 2000 with
>> >> Windows
>> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> >> Server
>> >> instance running). The SA has upgraded the memory of both nodes to 8G
>> >> each.
>> >> And I am tempted to use AWE to boost my SQL Server performance. The
>> >> server
>> >> is dedicated to running SQL Server.
>> >>
>> >> After AWE is enabled, can I configure my SQL Server max memory to
>> >> something
>> >> like 7G?
>> >> Concern is, while it may run OK during normal operating mode, what
>> >> will
>> >> happen in case both SQL Servers are moved to one node?
>> >>
>> >> Thanks very much in advance.
>> >> Sean
>> >
>|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32? API
>> that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and
>> release memory from the operating system as needed. Dynamic memory
>> allows you to set a maximum amount of physical memory that the operating
>> system can allocate to SQL Server. This allows the remaining memory to
>> be reserved for other purposes, such as the operating system and other
>> SQL Server instances. Fixed memory allows you to set a specific amount
>> of physical memory that SQL Server can use. This amount neither grows
>> nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
--
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>