Knowledge Base |

# PRB: Can Not Alter TEMPDB When in RAM

Article ID: 104714

Article Last Modified on 10/16/2003

APPLIES TO

- Microsoft SQL Server 4.21a Standard Edition, when used with:
  - Microsoft Windows NT 4.0

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.

Additional query words: 4.20

Keywords: kbother KB104714