Movement of an SQL Server Temp Database

INTRODUCTION

The tempdb, being a system database, is used by SQLServer to store internal objects such as the intermediate results of a query. The data pages of the tempdb are moved to and fro disk to as its being accessed by SQLServer hence should be placed on a drive which yields a good I/O speed.

As a system database, any activity to be carried out must be a Microsoft-recommended

technical line of action - detailed below.

Reason: Usually, the biggest reason that triggers the need for the movement of the tempdb is issue of limited disk space which in the short term can be fixed by performing a restart of the database instance to reclaim temp space. 

The tempdb size grows with verbose resultsets queried from the database.It also increases when sorting is carried on a user database and also when there are open transactions. Running the health-check operation DBCC Checkdb can also balloon the tempdb when it runs too long. 

PREREQUISITES

1. The new location should be accessible i.e. writable.

2. The windows/SQLServer profile should have the privilege to update database file attributes.

3. The database should be running in a full and good health

PROCESSES

1.   Determine the logical file names(data and log file) of the tempdb database and their current location on the disk.Find below the sql script and the logical names of the data and log files

SELECT name, physical_name AS   Current_Location FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');

          

GO                                

2.  Modify the location of each file (data and log files) by using ALTER DATABASE command on the master database.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'E:\users\annang\db_bag\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'E:\users\annang\db_bag\templog.ldf');

GO

3. Now, perform the activities below:

    a.      Move the physical data and log files to new location.

    b.      Stop and start the instance of SQLServer.

    c.       Verify availability of SQLServer