How to change the file location of Tempdb database - SQL Server

What is tempdb?

The tempdb database is a global resource that is available to all users connected to the instance of SQL Server System. This tempdb database is maintains user defined global or local temporary tables, temporary stored procedures, table variables, cursors along with database engine defined versions of the tables for snapshot isolation and temporary sorted rowsets when rebuilding indexes.

Tempdb database information is stored in the tempdb.mdf data file and templog.ldf log file. Tempdb database size is effect on the performance of user defined database operation. Temporary stored information is dropped automatically when shut down SQL Server System and tempdb is re-created automatically when SQL Server is started so the system starts with a clean copy of the database.

More Reference URL

http://msdn.microsoft.com/en-us/library/ms190768.aspx

Why would you change tempdb file location?

Few Reasons:

1) tempdb Drive full
2) disk corrupt issue
3) client request to move the files to specified location
4) tempdb should be on a SATA drive instead of an IDE drive and should not be on the same drive as the SQL Server software or the operating system

Optimizing tempdb performance 

More Reference URL

https://technet.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx

How to change the file location of Tempdb database

       

By default, Tempdb is placed on the same drive that SQL Server is installed on. 

a) Open SQL server SSMS and click new query
b) Run the below T-SQL Script to find the folder location of Tempdb database files.

USE TempDB

GO

EXEC sp_helpfile

GO

c) Execute the below T-SQL script to change the path of tempdb by providing new folder location for tempdb files

USE master; 

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Tempdb folder\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'D:\Tempdb folder\templog.ldf');

GO

d) Now, Go to the SQL server configuration manager and then Select SQL Server Services.

e) Stop and restart SQL Server (MSSQLSERVER)

f)  Execute the following to verify that tempdb was moved to the desired location

Regards,

Ganapathi varma

Senior SQL Engineer, MCP

Reference: (http://mssqllover.blogspot.in)

Linkedin