How to Move TempDB to Another Drive & Folder

Every time I have to do this, I Google for a script. I might as well write my own and put it here so at least I find myself in the Google results:

The results:

Moving TempDB

I can then copy/paste the results into SSMS, edit them, and run ’em. I like generating scripts like this rather than flat-out executing it automatically because sometimes I need to tweak specific file locations.

I’m not bothering with detecting what the file names were before, and I’m just generating new file names to match the database object’s name instead.

Erik says: See that path? Make sure you change that path to the right one. Preferably one that exists, and that SQL Server has permissions to. If you don’t, your server won’t start up unless you Google “how to start SQL Server without tempdb” and spend the next morning explaining to your boss why you should keep your job. I mean, I’ve heard stories…

Previous Post
How to Track SQL Server Changes with sp_BlitzFirst
Next Post
[Video] Office Hours 2017/11/22 (With Transcriptions)

29 Comments. Leave new

  • Erik; tell us more about those stories… ^_^

    Reply
  • Haha haha, Erik I’ll like to hear your story….

    Reply
  • hi,
    in this blog https://blogs.technet.microsoft.com/dataplatforminsider/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions/ they are talking about moving the tempdb to the temporary d-drive, but how is that working then when this tempdb got deleted?

    Reply
    • Janjansen – if TempDB doesn’t exist on startup, SQL Server will create it as long as the folder exists. If you use ephemeral/local SSD in IaaS (like Azure VMs, Amazon EC2, or Google Compute Engine), you just need to make sure the TempDB folders are created on Windows startup before SQL Server starts.

      Reply
  • That post just described a day in my life last week. And Erik’s post was the icing on the cake. Really annoying that SQL won’t let you refer to a drive that doesn’t exist when you’re also trying to change drive letters at the same time.

    Reply
  • Moved a comma and added a Tab character, so the output rows line up with themselves, for that sweet sweet Alt-key block-selecting fun:

    SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.[name] + ‘]’
    + CHAR(9) /* Tab */
    + ‘,FILENAME = ”Z:\MSSQL\DATA\’ + f.[name]
    + CASE WHEN f.[type] = 1 /* Log */ THEN ‘.ldf’ ELSE ‘.mdf’ END
    + ”’);’
    FROM sys.master_files f
    WHERE f.database_id = DB_ID(N’tempdb’);

    Reply
  • Bad Brent. You put your LDF and MDF on the same drives… 🙂 Just picking. I have constantly searched Google for these answers. In this job I finally created a Folder in my scripts folder of “Useful Tools” (also includes Adam Machanic, Ola’s, and your scripts as well).

    Reply
  • Maybe it’s an age/cultural thing, but, rather than rely on Google… I just have a “toolbox” folder for scripts like this, so I don’t have to look one up every time I need it.

    Although the “move tempdb.sql” file in my toolbox is dated 2009 and references a URL for KB 224071 that doesn’t seem to work anymore, so I guess there’s an argument for forcing one’s self to check for something more recent! (Does one still need to stop and restart SQL Server, as I noted in comments to myself? I haven’t had to move a tempdb in a long time, I guess…)

    Reply
  • […] to move tempdb from one drive to another to free up space. So I googled to find a script and found this one by Brent Ozar. It has been added to StackExchange and refined a […]

    Reply
  • I need to change the path and edit my .mdf and .ndf file name of temdb.

    can anyone please help

    Reply
  • Fernando Paez Becker
    May 18, 2018 8:55 am

    Brent, in case you put a wrong unit, you can run a subst command… and after that, make corrections in a proper maintaining windows, it’s a fastest way.

    Reply
  • I updated this script to allow you to change the file / folder locations at the top :

    DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
    , @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’

    SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
    + CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = ”’ + @TempLog + f.name ELSE ‘ FILENAME = ”’ + @TempData + f.name END
    + CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
    + ”’);’
    FROM sys.master_files f
    WHERE f.database_id = DB_ID(N’tempdb’);

    Reply
    • You meant:
      DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
      , @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’

      SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
      + CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = “‘ + @TempLog + f.name ELSE ‘ FILENAME = “‘ + @TempData + f.name END
      + CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
      + ‘);’
      FROM sys.master_files f
      WHERE f.database_id = DB_ID(N’tempdb’);

      Reply
      • Note that you still have to correct the quotation marks to the correct single and double quote instead of the left/right quotes.

        Reply
        • Never mind. Still messed up.

          Reply
          • DECLARE @TempData varchar(2000) = ‘F:\MSSQL\DATA\’
            , @TempLog varchar(2000) = ‘H:\MSSQL\DATA\’

            SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
            + CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘ FILENAME = ”’ + @TempLog + f.name ELSE ‘ FILENAME = ”’ + @TempData + f.name END
            + CASE WHEN right(f.physical_name, 3) = ‘ldf’ THEN ‘.ldf’ WHEN right(f.physical_name, 3) = ‘ndf’ THEN ‘.ndf’ WHEN right(f.physical_name, 3) = ‘mdf’ THEN ‘.mdf’ END
            + ”’);’
            FROM sys.master_files f
            WHERE f.database_id = DB_ID(N’tempdb’);

            –Corrected the quotes. But probably still have the left/right quotes

  • […] need an ALTER statement per file – if copy/paste isn’t fast enough, here’s a post by Brent Ozar to script out the […]

    Reply
  • JorgePerezUY
    March 21, 2019 5:24 am

    I think that the secondary files of the type rows data must have .ndf extension. So, this will be the correct query:
    SELECT ‘ALTER DATABASE tempdb MODIFY FILE (NAME = [‘ + f.name + ‘],’
    + ‘ FILENAME = ”H:\TempDB\’ + f.name
    + CASE WHEN f.type = 1 THEN ‘.ldf’ ELSE ‘.mdf’ END
    + ”’);’
    FROM sys.master_files f
    WHERE f.database_id = DB_ID(N’tempdb’);

    Please note that I only change de CASE statement.

    Reply
  • Rick Willemain
    July 9, 2019 3:19 pm

    Thank you ,,,, as always the best !

    Reply
  • Thanks Brent. Useful stuff.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}