INF: How to Move Tempdb to a Different Device

• Microsoft SQL Server version 6.5

SUMMARY

By default, tempdb is on the master device. You may want to move tempdb so that it resides entirely on another device. This article explains how to move tempdb onto a different device.

MORE INFORMATION

The basic procedure for moving tempdb to a different device is to first put tempdb in RAM, then make sure the device you want to put it on is the only default device (and has at least 2 MB of available space), and finally take tempdb back out of RAM. You can do this either from the SQL Enterprise Manager (SEM) GUI tool or by issuing ISQL commands. Each method is described below.

NOTE: Each method described here includes steps for creating a new device on which to place tempdb. However, you can also use an existing device, without creating a new one. If you want to use an existing device, skip the steps in the following procedures that deal with the creation of a new device and follow the rest of the steps. Also, if you decide to use an existing device, make sure it has at least 2 MB of free space available for when tempdb is moved back out of RAM.

Moving Tempdb by Using SQL Enterprise Manager

1. Make sure you have a current backup of the Master.dat file. To do this, shut down SQL Server and copy the Master.dat file to another location.



2. Start SQL Enterprise Manager. Create a new device for tempdb by doing the following:
   
   
   1. Right-click the Database Devices folder and click New Device on the shortcut menu. Alternatively, you can click Database Devices on the Manage menu and then click the New Device toolbar button.
   
   
   
   2. In the Name box, type the name "TEMPDEVICE" (without the quotation marks) and then click to select the Default Device option.
   
   
   
   3. In the Size box, type the number of megabytes you want your new device to be (for example, type 100 for a 100-MB device).
   
   
   
   4. Click Create Now.
   
   
   



3. Configure tempdb to temporarily reside in RAM. To do this, perform the following steps:
   
   
   1. Right-click the server name and click Configure on the shortcut menu. Alternatively, you can click the Server menu, point to SQL Server, and then click Configure.
   
   
   
   2. Click the Configuration tab.
   
   
   
   3. Scroll down until you see the "tempdb in RAM (MB)" configuration option. In the Current column, type a number of megabytes of RAM to allocate to tempdb (for example, 2). This will only be temporary. Then click OK.
   
   
   



4. Prevent the master device or any other devices from being a default device. To do this, perform the following steps:
   
   
   1. Under the Database Devices folder, double-click the master device or right-click the master device and click Edit on the shortcut menu. Alternatively, you can click Database Devices on the Manage menu, select the master device, and then click the Edit Device toolbar button.
   
   
   
   2. Click to clear the Default Device check box, and then click Change Now.
   
   
   
   3. Repeat Steps a-b for any other default devices you may have, except for the TEMPDEVICE created in Step 2 of this procedure.
   
   
   



5. Shut down and restart SQL Server. Tempdb should now be in RAM and not on the master device.



6. Take tempdb back out of RAM. To do this, repeat Step 3 of this procedure, but type 0 in the Current column for the "tempdb in RAM (MB)" configuration option.



7. Shut down and restart SQL Server again. Because master is no longer a default device, tempdb will go onto your new device that is specified as the default device (that is, TEMPDEVICE).



8. After restarting SQL Server, tempdb will have the default options set and a default size of 2 MB. You can change the tempdb database options and expand its size by performing the following steps:
   
   
   1. Under the Databases folder, double-click tempdb or right-click tempdb and click Edit on the shortcut menu. Alternatively, you can click Databases on the Manage menu, select tempdb, and then click the Edit Database toolbar button.
   
   
   
   2. On the Options tab, verify that the following options (which are desirable in most cases) are set:
      
      
      • Select Into/Bulk Copy is enabled.
      
      
      
      • Truncate Log On Checkpoint is enabled.
      
      
      
      • Single User is disabled.
      
      
      
      • DBO Use Only is disabled.
      
      
      
   
   
   
   3. On the Database tab, in the Size box, click Expand.
   
   
   
   4. Under Data Device, select TEMPDEVICE.
   
   
   
   5. In the Size (MB) box, type a number of megabytes to be added to tempdb. For example, if you type 8, it adds 8 MB to tempdb, for a total size of 10 MB.
   
   
   
   6. Click Expand Now.
   
   
   
   For more information on expanding the size of a database, see the "Expanding or Shrinking Databases" topic in the SQL Server Books Online.

Moving Tempdb by Using ISQL

1. Make sure you have a current backup of the Master.dat file.



2. Create your new device for tempdb, using a statement similar to the following:
   

   
         DISK INIT
         NAME = 'TEMPDEVICE',
         PHYSNAME = 'C:\MSSQL\DATA\TEMPDEV.DAT',
         SIZE = 51200,
         VDEVNO = 250
     

   NOTE: This example creates a 100-MB device for tempdb. You can adjust the SIZE option as desired.



3. Issue the following statements, which will put tempdb into RAM, prevent the master device from being a default device, and make the newly-created tempdevice into a default device:
   

   
         sp_configure 'allow updates', 1
         go
         reconfigure with override
         go
         sp_configure 'tempdb in ram', 2
         go
         sp_diskdefault master, defaultoff
         go
         sp_diskdefault tempdevice, defaulton
         go
         reconfigure with override
         go
     

   NOTES:
   
   
   • The sp_configure 'tempdb in ram' configuration option is specified in MB, not 2-KB pages, as the 'memory' parameter is.
   
   
   
   • If you have other default devices, you should also run sp_diskdefault with the defaultoff option for those devices as well. To see if you have other default devices, you can run the sp_helpdevice stored procedure.
   
   
   



4. Shut down and restart SQL Server. Tempdb should now be in RAM and not on the master device.



5. Now use sp_configure to take tempdb back out of RAM:
   

   
         sp_configure 'tempdb in ram', 0
         go
         reconfigure with override
         go
     




6. Shut down and restart SQL Server. Because master is no longer a default device, tempdb will go onto your new device.



7. After restarting SQL Server, tempdb will have a default size of 2 MB. You can expand its size by using an ALTER DATABASE statement similar to the following:
   

   
         ALTER DATABASE tempdb
         ON TEMPDEVICE = 8
     

   This statement increases the size of tempdb by 8 MB, for a total size of 10 MB. For more information on the ALTER DATABASE statement, see the "ALTER DATABASE Statement" topic in the SQL Server Books Online.



8. Verify that the following database options are set for tempdb:
   
   
   • Select Into/Bulk Copy is enabled.
   
   
   
   • Truncate Log On Checkpoint is enabled.
   
   
   
   • Single User is disabled.
   
   
   
   • DBO Use Only is disabled.
   
   
   
   To do this, use the sp_dboption stored procedure. For more information on how to use sp_dboption to view or change database options, see the "sp_dboption System Stored Procedure" topic in the SQL Server Books Online.

Q115050 : INF: When to Use Tempdb In RAM

Q158586 : PRB: 'Temp_db' Device Causes Problems Starting SQL Server

Q141183 : PRB: Log Segment Moved to Device Incorrectly if Tempdb Expanded

Q110139 : INF: Causes of SQL Transaction Log Filling Up

Additional query words: dev temp db

Keywords :
Version : WINNT:6.5
Platform : winnt
Issue type : kbhowto kbinfo