Monthly Archives: May 2013

Imaprted

There may come a time when you might want to shrink tempdb because it has become too large.

There are a few ways you can do this and I have listed them below but please read to the end of the post before making a decision on which way you want to approach this. There is an important note at the end of the post.

Don’t try and increase filesizes in tempdb using this command because you will see an error. In this example, the attempt was to increase to 50Mb.

Cannot shrink file '1' in database 'tempdb' to 6400 pages as it only contains 640 pages.

How to shrink tempdb using DBCC SHRINKDATABASE

The syntax is as follows and does not require a restart of the SQL Server service:

DBCC SHRINKDATABASE(tempdb, ‘target_percentage_of_free_space’);

So if the data files in tempdb had enough free space, you could shrink tempdb by running this command to leave 10% of free space at the end of the files:

1DBCC SHRINKDATABASE(tempdb, 10);

How to shrink tempdb using ALTER DATABASE

As in my post about moving tempdb, you can use the ALTER DATABASE command to perform a tempdb resize. A restart of the SQL Server service will shrink tempdb to its original predefined size but you can also resize tempdb using ALTER DATABASE.

The following script will resize both the log and data file to be 100Mb.

1USE master;

2GO

3ALTERDATABASEtempdb

4MODIFYFILE (NAME= tempdev, SIZE=100Mb);

5GO

6ALTERDATABASEtempdb

7MODIFYFILE (NAME= templog, SIZE=100Mb);

8GO

How to shrink tempdb using Management Studio

You can also use Management Studio to resize Tempdb and perform shrink operations by right mouse clicking the tempdb database and choosing Tasks->Shrink.

To resize tempdb, you can set the file sizes by right mouse clicking the tempdb and choosing Properties->Files and setting the sizes there.

Note that with both the ALTER DATABASE and management studio methods, you cannot resize a tempdb file to a particular size if the data contained in the file exceed the size that you are trying to size to.

Should you shrink TempDB?

It is documented in this Microsoft article that it can cause consistency errors in the TempDB database if you perform a shrink operation while the database is in use so please read this carefully and consider whether you can shrink the database by other means, i.e restarting the SQL Server instance which will create a brand new copy of TempDB releasing the disk space.