Search This Blog

Relocate and Re-size the TempDB

Often times it becomes necessary to move the tempdb.  Maybe your data files are becoming too large, or you want to relocate the files to achieve more optimal performance.  It is fairly easy to move the tempdb, but it will require a restart of the SQL Server service.  In this example I have both changed the location of the files, and added a couple of new ones.  The end result is a completely isolated TempDB -- on it's own drive, and separate from the user and other system databases.

Let's run this first to confirm the logical file names for the tempdb data files: 
 USE tempdb;
 EXEC sp_helpfile

Your results will look something like this:
name      fileid   filename                       filegroup        size        maxsize    growth    usage
tempdev       1            C:\MSSQL\Data\tempdb.mdf       PRIMARY        1092 KB   Unlimited   10%         data only
templog        2            C:\MSSQL\Log\templog.ldf         NULL                512 KB   Unlimited   10%         log only

The first step is to move TempDB to its own drive. I only set a 1mb file size because SQL Server does something a little screwy here. It does not matter if we tell it to use a different drive letter. SQL will still look for the given amount of free space on the drive that TempDB currently sits on. So, if SQL is installed at C:\Program Files\, and we try to create a 10GB TempDB file on a different drive, the server will look for 10GB on the C drive. Its a bug I learned about here:
      http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-2-after-the-install/
   -- Step 1.
   USE master;
   GO
   ALTER DATABASE TempDB MODIFY FILE (
      name = 'tempdev', 
   filename = 'F:\MSSQL\TempDB\tempDB.mdf', 
   size = 1MB
   )
   GO

   ALTER DATABASE TempDB MODIFY FILE (
   name = 'templog', 
   filename = 'F:\MSSQL\TempDB\templog.ldf', 
   size = 1MB
   )
   GO

At this point, you will need to restart the SQL Server service, in order for the changes to take effect. Take a look at the F:\ drive (or whatever you used), and note your new files. 

The second step is to expand the TempDB data file to the correct full size, and create three additional TempDB files. I always use Paul Randal's advice, which is to make one data file for every processor core. Sometimes I need more, but I pretty much always start with 4 and go from there.

You can see that I expanded tempdev to 250MB, and created the 3 new files at 250MB each, with 0 FILEGROWTH.  This is VERY important.  We don't want them to auto-grow frequently in production, because this causes disc fragmentation, and impedes activity during the file growth.  Ideally, we preallocate TempDB to take whatever disc we have reserved for it.  Basically, build it proactively, not re-actively.  

  -- Step 2.
  -- Expand tempdev
    USE master;
    ALTER DATABASE TempDB MODIFY FILE (
   name = 'tempdev', 
   filename = 'F:\MSSQL\TempDB\tempDB.mdf',
   size = 250MB,
   filegrowth = 0
     )
     GO

   -- Add three new data files
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev2', 
   filename = 'F:\MSSQL\TempDB\tempdev2.ndf', 
   size = 250MB,
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
      name = 'tempdev3', 
   filename = 'F:\MSSQL\TempDB\tempdev3.ndf', 
   size = 250MB,
   filegrowth = 0
     )
     GO
     USE master;
     ALTER DATABASE TempDB ADD FILE (
   name = 'tempdev4', 
   filename = 'F:\MSSQL\TempDB\tempdev4.ndf',
   size = 250MB,
   filegrowth = 0
     )     
     GO

Now I will rename the original 'tempdev' data file to 'tempdev1'.  This is totally NOT a requirement. I just like to keep all of my files consistently named.  (tempdev1, tempdev2, tempdev3, etc.):

   /* Rename files */
   ALTER DATABASE tempdb
   MODIFY FILE (
       NAME ='tempdev', 
       NEWNAME = 'tempdev1'
   )

Lastly, I will increase the size of the tempdb log to 2GB.  Remember, this is just an example.  The size of your tempdb log will be dictated by your SQL Server utilization.

   /* Increase TempDB Log file.  */
   ALTER DATABASE tempdb
   MODIFY FILE (
      NAME = 'templog', 
      SIZE = 2048MB
   );


There are many other different references out there on tempdb performance, such as the ones I've linked to below.   I encourage you to do some research.  TempDB is not something where we have a lot of room for errors.  Take a look at these references, and let me know if I can help any further.