Relocate and Re-size the TempDB

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.

      Optimizing tempdb Performance 
      Do you need more TempDB files?

Temp Table vs Table Variable vs CTE and the use of TEMPDB.

There are many reasons that a Temp Table, Table Variable or Common Table Expression can be a good solution, but there is also a lot of confusion around these.  Much of that confusion is around the use of TempDB.

For the purpose of this article I am going to use my standard Departments table from my CTE presentation. It is just a simple database to work with. Also, this is running on my local SQL Server with no other users connected.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

USE [Master]; set statistics io off; IF EXISTS(SELECT name FROM sys.databases WHERE name = 'cte_demo') BEGIN  ALTER DATABASE [cte_demo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE [cte_demo]; END CREATE DATABASE [cte_demo]; GO USE [cte_demo]; -- create a table to use for CTE query demo CREATE TABLE [Departments] (  id int, --would normally be an INT IDENTITY  department VARCHAR (200),  parent int  ); -- insert top level departments insert into [Departments] (id, department, parent) values (1, 'Camping', null); insert into [Departments] (id, department, parent) values (2, 'Cycle', null); insert into [Departments] (id, department, parent) values (3, 'Snowsports', null); insert into [Departments] (id, department, parent) values (4, 'Fitness', null); -- now some sub-departments for camping insert into [Departments] (id, department, parent) values (5, 'Tents', 1); insert into [Departments] (id, department, parent) values (6, 'Backpacks', 1); insert into [Departments] (id, department, parent) values (7, 'Sleeping Bags', 1); insert into [Departments] (id, department, parent) values (8, 'Cooking', 1); -- now some sub-departments for cycle insert into [Departments] (id, department, parent) values (9, 'Bikes', 2); insert into [Departments] (id, department, parent) values (10, 'Helmets', 2); insert into [Departments] (id, department, parent) values (11, 'Locks', 2); -- now some sub-departments for snowsports insert into [Departments] (id, department, parent) values (12, 'Ski', 3); insert into [Departments] (id, department, parent) values (13, 'Snowboard', 3); insert into [Departments] (id, department, parent) values (14, 'Snowshoe', 3); -- now some sub-departments for fitness insert into [Departments] (id, department, parent) values (15, 'Running', 4); insert into [Departments] (id, department, parent) values (16, 'Swimming', 4); insert into [Departments] (id, department, parent) values (17, 'Yoga', 4);

Then to compare the difference on the TempDB usage, I am going to use an under-documented function called fn_dblog() which shows you a list of the items that are written to the transaction log in different scenarios.  I will also combine this with the CHECKPOINT command to clear the contents of the log for TempDB.

To try out out, run the following on a TEST or DEVELOPMENT database.  Don’t run this on your production system.

1 2 3 4 5 6 7 8 9 10

USE TempDB; GO SELECT *  FROM fn_dblog(null, null); CHECKPOINT; SELECT *  FROM fn_dblog(null, null);

Then take a look at the output which will look something like this.

The first time you select from fn_dblog() you may be many rows.  In my example fn_dblog() returned 171 rows, you may have many more.  But after running CHECKPOINT you only get 3 rows, and those are the 3 rows that you will normally get after running CHECKPOINT.