• Article Topics

  • Become a member of the SQLDBASupport community and receive notifications of new posts by email

    Join 82 other followers

  • Are you writer? Become an Author! Mail to

    amitvaid81 @ gmail dot com

  • Founder

    This is journey of a SQL DBA. I now work as a Sr. Database Architect and Solution Consultant, generally in performance tuning, clustering or high availability. The opinions expressed herein are my own personal opinions through real time experience.

    Amit Vaid

  • Advertisements

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


– 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

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
(NAME = tempdev, FILENAME = ‘d:\data\tempdb.mdf’)
(NAME = templog, FILENAME = ‘E:\log\templog.ldf’)

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.



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: