SQL Server : How to move or add Tempdb data and log files


Problem:

– TempDB has grown big and the current disk drive does not have enough space.
– Adding TempDB data file to another physical drive helps to improve performance, as they can be read simultaneously.

Solution : Here I have tried to explain how can we move tempdb data files from one place to another.

Open Query Analyzer and Run this script to get the names of the files used for TempDB.
sp_helpdb tempdb
GO

Results will be something like:
name          fileid       filename                                                                                                                              filegroup       size
——- —— ————————————————————– ———- ——-
tempdev    1               C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf     PRIMARY      204800 KB
templog     2               C:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf       NULL               1024 KB

You notice that names of the files are usually tempdev and templog by default. Now you need to use following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘d:\data\tempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = ‘E:\log\templog.ldf’)
GO

You will get the below output after executing the above script

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Thanks

Advertisements

One Response

  1. It’s the best time to make some plans for the future and it’s time to be
    happy. I’ve read this post and if I could I want to suggest you few interesting things or advice. Maybe you could write next articles referring to this article. I desire to read even more things about it!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: