Configuring SQL Server procedure memory

Category: Frequently-asked questions
Date: 16 Dec 2010
Product: SQL Backup
SQL Server provides a limited amount of memory for stored procedures and OLE objects. SQL Backup uses this memory for buffering and other operations, so it is important that the procedure memory is correctly configured so that it is not exhausted by SQL Backup.

SQL Backup needs 6*MAXTRANSFERSIZE memory from SQL Server's memory space for each thread in order to do the backup. This memory needs to be contiguous. If you set MAXTRANSFERSIZE all the way down to 64KB and the backup still fails, then there just isn't enough contiguous memory. You can check the contiguous memory using the master..sqbmemory stored procedure.

There are lots of reasons why SQL Server runs out of memory. Other extended stored procedures, COM objects allocated out of stored procedures using sp_oaCreate that are not freed, and memory configuration problems come into play. SQL Backup can also consume the memory quickly on multithreaded or split-file backups, in which case it may be advisable to reduce the number of threads.

According to Microsoft, for 32-bit editions of SQL Server, it is important to make sure that the /3GB and /PAE switches are set in accordance with the amount of physical memory in the system startup.

  · If you have 3-4 GB memory, include the /3GB switch in the startup.
  · If you have 4-8GB memory, use /3GB and /PAE
  · If you have 16 GB or more, use only /PAE, as /3GB will cripple memory over 8GB.

· When you have set /PAE, go into SQL Server's configuration and set the option to use AWE to ON. If you do this, however, you need to also specify a maximum memory value in SQL Server, if you do not, then SQL Server will take all but 128MB of the computer's memory if the automatic memory management is used in SQL Server.

· The user who runs the SQL Server needs to have the 'lock pages in memory' user right in the local security policy, or it will have problems allocating the memory for SQL Backup's extended stored procedure. If you have checked everything above, please check this as well.

Note: For 64-bit editions of SQL Server 2005, only SQL Server 2005 Enterprise Edition can use the Lock pages in memory user right.

· There is a -g startup option in SQL Server 2000 to control how much memory SQL Server will leave free for extended stored procedure code. This is important to do when you have more than 500 databases on a server: each database that's online will use 64Kb of the free memory. Microsoft recommends:

500 databases: -g288
1000 databases -g372

After setting -g, you need to restart SQL Server. Also, AWE is not available on all editions of SQL Server. If you have hundreds of databases on a SQL Server Standard, think about going to Enterprise or get another instance of SQL to hold the databases.