Tempdb is one of the system databases in MS SQL Server. It is used by all the users who are connected to an instance to hold the temporary objects like global and local temporary tables, table variables, interim data for sorting and grouping operations. In SQL Server 2005, it also holds the row versions of the data affected by newly introduced features like online index operations.
It gets re-created every time when SQLServer instance is started. Normally temporary tables or any other object exists in the tempdb only during its scope. i.e. once procedure execution is finished, any temporary tables or table variables created by procedure are dropped from tempdb. On the other hand, global temporary tables are removed from the tempdb when last session accessing the table is closed. To see how temporary tables work, you can read our previous blog post on this topic. Another post covers replacement of temp tables with table variables and also, the scope differences for table variables.
In short when an instance is shut down, there are no active connections and hence there is nothing in the tempdb. So there is no need to take backup of tempdb. In fact, backup and restore operations are not allowed on tempdb.
With this introduction on tempdb, let us see how can, we move tempdb to a different location from it’s default location. Some times it is necessary to resize and move temporary database (tempdb) to separate disk for performance improvements. In one of our previous blog post, we showed how we can move datafile of temporary tablespace to different location in Oracle. In this article, we will show you how we can move tempdb database in MS SQLServer.
Connect to an instance either using management studio or query analyzer. Let us first check physical location and logical name of the existing tempdb files.
— FOR SQLServer 2005
WHERE database_id = DB_ID(N’tempdb’);
— For SQLServer 2000. This query can be used in SQLServer 2005
— as well.
Following will be the output from the first query. We have modified the result set to fit into the page.
name physical_name size max_size
——- ———————— —– ——-
tempdev C:\MSSQL\DATA\tempdb.mdf 1024 -1
templog C:\MSSQL\DATA\templog.ldf 64 -1
In the above result set,:
• name is the logical name of the file.
• Physical_name is current location where data and log file resides. It has been modified to fit into page for better display.
• Size is the file size shown in terms of 8KB pages.
• Max_size indicates maximum size of the file. -1 indicates, that we allow file to grow until disk is full.
Now issue the following command to change the size of the data and log file and also specify new path to put files in new location. If one does not want to change the file size, SIZE parameter can be omitted. Make sure that you change the FILENAME parameter value appropriately before executing the command.
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev,
FILENAME = ‘D:\SQLServer\Data\tempdb.mdf’)
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog,
SIZE = 500 MB,
FILENAME = ‘D:\SQLServer\Data\templog.ldf’)
Even though command is executed successfully, change will not be immediate. We need to stop and restart SQL Server service. Next time upon instance startup, tempdb will be created using files in new location.
Once instance is up and running, we need to delete old tempdb files from its original location. Let us verify that change has taken place by issuing following command. New values for location and size will be displayed along with other related values.
Appropriate sizing and placement of tempdb will definitely help in resolving tempdb performance bottlenecks. In our future posts, we will discuss how to avoid performance issues related to tempdb – we will look at hardware optimization and placement of tempdb, number of files created for tempdb, proper file sizes and code optimizations.