PRB: Can Not Alter TEMPDB When in RAM

  - Microsoft SQL Server version 4.2 for Windows NT

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.