Published on

June 11, 2008

Understanding TempDB in SQL Server

TempDB is a special database in SQL Server that is used to store temporary objects such as temporary tables, table variables, and cursors. It is a crucial component of the SQL Server system and plays a significant role in query processing and transaction management.

Recently, I received a question about moving TempDB and how it behaves when the SQL Server service is restarted. Moving TempDB is not as straightforward as moving a regular database, so let’s explore the process and understand what happens behind the scenes.

Typically, when we want to move a database, we detach it, cut/paste the files to the new location, and then reattach it. However, this approach doesn’t work for system databases like TempDB since they are constantly in use. So, we need to find an alternative method.

The recommended approach to move TempDB is to use the ALTER DATABASE statement with the MODIFY FILE clause. Here’s an example of how to move TempDB to the C:\ drive:

USE master;
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'c:\tempdb.mdf');
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'c:\templog.ldf');

By executing these statements, we are essentially modifying the file locations in the system catalog of TempDB. To verify the changes, we can run the following query:

SELECT * FROM tempdb.sys.database_files;

After executing the ALTER statements, the query will show the new file locations. However, if you check the disk, you will notice that the files are still in the old location. This is because the actual file movement occurs when the SQL Server service is restarted.

When you stop and restart the SQL Server service, TempDB will be created in its new location, and the old TempDB files will still exist at the original location. You can safely delete the old TempDB files once the service has been stopped.

It’s important to note that any objects created in TempDB will not persist across a service restart. For example, if you create a table in TempDB and then restart the service, the table will no longer exist. To ensure the persistence of objects, you can either add them to the Model database or execute a startup script that recreates them.

Moving TempDB is not something you’ll need to do frequently, but it’s good to know the process and understand how it works. Once you are familiar with the syntax and the behavior, it becomes a relatively painless task.

For more SQL Server tips and insights, visit my blog at http://blogs.sqlservercentral.com/andy_warren/default.aspx.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.