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

Last reviewed: April 16, 1997
Article ID: Q166350

The information in this article applies to:

Microsoft SQL Server, versions 6.0 and 6.5

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.
```
Once 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 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.

Keywords : kbusage SSrvAdmin SSrvGen
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.