PRB: SQL Server Won't Start After Setting TEMPDB IN RAM Too High
ID: Q166350
|
The information in this article applies to:
-
Microsoft SQL Server versions 6.0, 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.
- 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 : kbusage SSrvAdmin SSrvGen
Version : WINNT:6.0,6.5
Platform : winnt
Issue type : kbprb