Monday, June 23, 2008

Not be enough virtual memory - SQL Server

For each updated database, SQL Server allocates at least one 64-KB block for use in formatting log records before they are written to disk. This allocation occurs when the first log record is generated for the database, such as during an INSERT, UPDATE, or DELETE statement. Depending on the activity and the size of the generated log records, subsequent modifications might trigger the allocation of additional 64-KB allocations. SQL Server 7.0 will allocate no more than three 54-KB blocks. In SQL Server 2000, the upper number of allocations for each database is a function of the number of processors that SQL Server is configured to use.

Solution
Use the -g startup parameter to leave additional, unreserved virtual memory available for these database allocations. The -g parameter is documented in the Readme.txt of the SQL Server 7.0 service pack, and in SQL Server 2000 Books Online. The "More Information" section in this article includes the settings that Microsoft recommends you use to determine the appropriate value for this setting.