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

ID: Q166350

The information in this article applies to:

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:

  1. 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.


  2. 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)


  3. 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


  4. Shut down SQL Server:
    
shutdown
go
    Server SHUTDOWN by request.


  5. Start SQL Server as normal.


Additional query words: temp db 


Keywords          : kbusage SSrvAdmin SSrvGen 
Version           : WINNT:6.0,6.5
Platform          : winnt 
Issue type        : kbprb

Last Reviewed: April 9, 1999