PRB: Can Not Alter TEMPDB When in RAM (104714)


The information in this article applies to:

  • Microsoft SQL Server 4.2x, when used with:
    • the operating system: Microsoft Windows NT
This article was previously published under Q104714

SYMPTOMS

Page 91 of the SQL Server for Windows NT Configuration Guide states that you can extend the size of the TEMPDB database while it is in RAM by using the ALTER DATABASE statement. Attempting to do so, however, results in the following error:
Database TEMPDB cannot be altered when in RAM. Remove from RAM with 'sp_configure', stop\restart SQL Server, perform 'ALTER DATABASE', place in RAM with 'sp_configure', stop\restart SQL Server
(Msg 5016, Level 16, State 1)

CAUSE

The ability to dynamically alter the size of TEMPDB when it is in RAM is not allowed for the first release of SQL Server on Windows NT.

WORKAROUND

As the error message indicates, the proper steps to use to increase the size of tempdb when it is in RAM are:
  1. Execute the following command to take TEMPDB out of RAM:

    sp_configure 'tempdb in ram', 0
  2. Shutdown SQL Server and restart it. The TEMPDB database will now be back at its default of 2MB on the master device.
  3. Use the ALTER DATABASE statement to increase the size of TEMPDB to the desired size.
  4. Execute the following command to place TEMPDB back in RAM:

    sp_configure 'tempdb in ram', 1
  5. Shutdown and restart SQL Server for the change to take effect. The TEMPDB database should now be back in RAM.
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbother KB104714
©2003 Microsoft Corporation. All rights reserved.