Microsoft KB Archive/166350

= PRB: SQL Server Won't Start After Setting TEMPDB IN RAM Too High =

Article ID: 166350

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q166350



SYMPTOMS
If the SQL Server tempdb in RAM value is configured to a value higher than the available RAM remaining on the computer, SQL Server fails on the next startup attempt. Because the configuration value does not take affect until the server is stopped and restarted, the server functions as normal until that time. Upon attempting to restart the server, the following message appears in the SQL Server error log:

kernel udactivate(IN_RAM): Operating system error 8(Not enough storage

is available to process this command.) encountered

spid1 Device activation error. The physical filename 'IN_RAM' may be

incorrect

spid1 crdb_tempdb: Unable to move tempdb into RAM; RAM device doesn't

exist, cannot be created, or doesn't have enough space for tempdb



CAUSE
On startup, if tempdb is configured to exist in RAM, SQL Server must create a temp_db device within the available system RAM in which to store tempdb. If the size of tempdb exceeds the available amount of system RAM, the device cannot be created. Without the device, SQL Server is unable to create tempdb in RAM. It is important to note that the tempdb in RAM value uses RAM in addition to memory currently allocated to SQL Server, and is specified in MB, not 2-KB pages.



WORKAROUND
To resolve this problem, do the following:

 Open a command prompt session and start SQL Server in minimal configuration mode with the following command:

sqlservr -c -f

You will get a screen dump of the error log. When the startup is complete, you will have a flashing cursor after the following line:

97/02/27 15:34:46.14 kernel Warning: override, autoexec procedures skipped.

 After the server is started, open a second command prompt session and connect to the server as the system administrator (SA) using ISQL:

isql -Usa -Ppassword (if you are not local to the computer, add -Sservername)  Reconfigure the tempdb in RAM parameter to an acceptable value. It is important to note that the tempdb in RAM value uses RAM in addition to memory currently allocated to SQL Server, and is specified in MB, not in 2-KB pages. To adjust the current value, use the following commands (where X is the value of tempdb in RAM, in MB):

sp_configure 'tempdb in ram', X go

Configuration option changed. Run the RECONFIGURE command to install.

reconfigure go   Shut down SQL Server:

shutdown go Server SHUTDOWN by request.  Start SQL Server as normal.

Additional query words: temp db

Keywords: kbprb kbusage KB166350

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.