Objective

Best practices call for tempdb to have one datafile per processor (core) and all to be of the same initial size and growth. This article shows a way to resizing the data files so that they all have the same initial size. The steps work with SQL Server 2005 (any version), but they might work with SQL Server 2008 as well, though as of the time of this writing, I have not tested it yet. Specific instructions are provided for clustered and non-clustered instances.

There are a few scenarios in which it might be necessary to re-size the datafiles:

1. Datafiles are too small for the actual usage. If this is the case you should increase the size to something more adjusted to he actual usage. The reason is that otherwise, the instance will incur in too many extents which leads to fragmentation of the datafile which in turn leads to slow I/O.
2. Datafiles are incorrectly sized or having different initial size in a multiprocessor instance. For example, you might have on a 4-processor machine, an instance with tempdb having four datafiles (one per processor), but 3 of 2MB and one of 5 MB. Let's say, the best size is 3MB, you would need to resize the 5MB datafile down to 3MB, and resize the others up to 3MB as well.

Determining the right size is not an exact science, but one could try. It is a combination of trial and error and good monitoring. Check out these two links for more. Working with tempdb:
http://technet.microsoft.com/en-us/library/cc966545.aspxand troubleshooting performance: http://msdn.microsoft.com/en-us/library/cc966540.aspx#EEAA

Assume the following scenario:

• 4 datafiles: tempdev, tempdev2, tempdev3, tempdev4
• Initial size for tempdev, tempdev2, tempdev3 is 2MB
• Initial size for tempdev4 is 5MB.
• The Target initial size for all datafiles is 3MB

When Target Initial size is greater than portion in use

If used portion of all datafiles is smaller than the Target Initial Size, then SQL Server allows upward re-sizing of the tempdb datafiles. This can be done both via the SSMS GUI or with T-SQL (see statement below). Once thing to keep in mind is that the new Initial Size must be greater than the Used portion of the Current Size.

A little detour is required here. There are three size concepts that are relevant here. Current Size, Used Size and Initial Size. Initial Size is the size the datafile is allocated when the instance starts up. Used Size is the size in use. Finally, Current size is the total currently allocated size of the datafile. Current Size is always equal or larger than the Initial Size. A much larger Current Size than the Initial Size is an indication of poor dimensioning of the datafile. The Used Size varies between 0 and the Current Size. To calculate Current and Used sizes run this:

select m.name
, m.size / 128.0 "Initial Size (MB)"
, d.size / 128.0 "Current Size (MB)"
, (fileproperty(m.name, 'spaceused'))/128.0 "Used (MB)"
from sys.master_files m join sys.database_files d
on m.file_id = d.file_id
where database_id = 2

So to increase the size to equalize the Initial Size across all datafiles, run the following for each datafile:

1. Increase the file size

alter database tempdb modify file (name = tempdev, size = 3 MB, filegrowth = 1 MB)
go

2. Repeat for each the remaining datafiles
3. Verify that the file size and growth are the same for all datafiles

use tempdb
go
select name
, size / 128.0 "Initial Size (MB)"
, is_percent_growth
, growth * 8 "Growth (KB)"
from sys.master_files
where database_id = 2

4. Restart SQL Server using SQL Server Configuration Manager (SSCM). Clustered instance Note: use Cluster Administrator instead of SSCM to take SQL Server offline and then to bring it online.

When Target Initial size is smaller than portion in use

SQL Server will not allow making a tempdb datafile smaller than its used portion of the datafile. Of course, this implies the datafile is in use. So to perform this size reduction, we must stop SQL Server and start with an already small tempdb, so it can be adjusted upward to the right size.

For that we need to stop and then start the SQL Server service with the /f switch (option), set the initial size of the file and then restart the service as usual. Here are the steps:

1. Stop SQL Server service

• Use SQL Server Configuration Manager (SSCM) to change the startup parameters. Clustered instance Note: If working with a failover-cluster, make sure to start SSCM from the Active node.
  1. Right click on SQL Server (instance)
  2. Go to Advanced tab
  3. Click the drop-down for Startup Parameters and insert this in front of existing startup options: -f;-m;
     Option -m will start the instance in Single-user mode while -f option will start the instance minimally configured including a tempdb database with one datafile, tempdev, of 1MB.
  4. OK
  5. Stop SQL Server. Clustered instance Note: use Cluster Administrator instead of SSCM to take SQL Server offline.

or you may use these steps (Use steps above if working with a clustered instance):

• From a DOS or PowerShell session, run this command to stop the SQL Server service:

  net stop "SQL Server"
  or
  net stop "SQL Server (namedinstance)"

  Use the second command if dealing with a named instance; replace namedinstance with the name of the instance.

2. Delete all tempdb data files except tempdev.mdf

Now we remove the datafiles from the file system, so they can be created with the new initial size. Remove all except tempdev.mdf which is the only file we need at this point because we will work with a minimally configured instance.

3. Start SQL Server minimally configured

• Use SQL Server Configuration Manager (SSCM) to start up the instance. Clustered instance Note: use Cluster Administrator instead of SSCM to bring SQL Server online.
• Or, if you used Method B in step 1 above, then should run:

  net start "SQL Server" /f /m
  or
  net start "SQL Server (namedinstance)" /f /m

  Use the second command if dealing with a named instance; replace namedinstance with the name of the instance.

  Note: you could also start the instance as an application from the command line using sqlservr.exe and the -f and -m switches (http://msdn.microsoft.com/en-us/library/ms190737%28SQL.90%29.aspx) or with net start and the /c /f /mswitches, see here: http://msdn.microsoft.com/en-us/library/ms162819.aspx

4. Start SQL Server Management Studio (SSMS) and connect to the instance:

Change file size to the new size for the first data file (in this case, four datafiles with 3 MB initial size).

-- modify existing tempdev with new initial size alter database tempdb modify file (name = tempdev, size = 3 MB, filegrowth = 1 MB)

go

Add additional data files (one per processor-core) and set their new initial size

-- remove all extra datafiles from the metadata
use master
go
alter database tempdb remove file tempdev2
go
alter database tempdb remove file tempdev3
go
alter database tempdb remove file tempdev4
go
-- add extra files with new initial size of 3 MB
use master
go
alter database tempdb add file (name = tempdev2, filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\tempdev2.ndf', size = 3 MB, filegrowth = 1 MB)
go
alter database tempdb add file (name = tempdev3, filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\tempdev3.ndf', size = 3 MB, filegrowth = 1 MB)
go
alter database tempdb add file (name = tempdev4, filename = 'c:\program files\microsoft sql server\mssql.1\mssql\data\tempdev4.ndf', size = 3 MB, filegrowth = 1 MB)
go
-- check initial size of all datafiles
use tempdb
go
select name
, size / 128.0 "Initial Size (MB)"
, is_percent_growth
, growth * 8 "Growth (KB)"
from sys.master_files
where database_id = 2

Verify that initial size and growth are set the same way across all data files.

5. Restart SQL Server

Restart SQL Server any of the two methods below.

• Use SQL Server Configuration Manager:
1. Stop SQL Server
2. Right click on SQL Server (instance)
3. Go to Advanced tab
4. Click the drop-down for Startup Parameters and remove ;-f;-m;
5. OK
6. Start SQL Server

• Or, if instance is not clustered, you may stop SQL Server using net stop as shown before. Then start SQL Server using net start but without using the options:

  net start "SQL Server"
  or
  net start "SQL Server (namedinstance)"

  Use the second command if dealing with a named instance; replace namedinstance with the name of the instance.

Conclusion

In multi-processor machines, it is best practice to have one datafile per processor (core) for the tempdb database and have them all with the same initial size and growth (see here for more on tempdb http://technet.microsoft.com/en-us/library/cc966545.aspx). I have shown here the steps to make those datafiles the same size from a scenario where that is not the case. Steps are provided for when the Target Initial Size of the datafiles is larger than the portion in use, and also for when the Target Initial Size of the datafiles is smaller than the portion in use of at least one datafile.