SQL: How to shrink tempdb

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.

So first, we’ll look at the configuration on my server

1 SELECT name, size
2 FROM sys.master_files
3 WHERE database_id = DB_ID(N'tempdb');
name                 size
-------------------- -----------
tempdev              1280
templog              640

(2 row(s) affected)

Note that the size column is listing the size of the file in 8Kb pages. In this instance my “tempdev” file is 10Mb (( 1280 * 8 ) = 10240 kb)

How to shrink tempdb using DBCC SHRINKFILE

The syntax is as follows and the operation does not require a restart of the SQL server service.

DBCC SHRINKFILE(logical_filename, size_in_MB)

So I will shrink the file to 5Mb

1 DBCC SHRINKFILE(tempdev, 5);

Which produces the following output and I can see that CurrentSize is now 50% smaller than previously

DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
2      1           640         288         176         176

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

name                 size
-------------------- -----------
tempdev              640
templog              640

(2 row(s) affected)

Don’t try and increase file sizes 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:

1 DBCC 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.

1 USE master;
2 GO
3 ALTER DATABASE tempdb
4 MODIFY FILE (NAME = tempdev, SIZE=100Mb);
5 GO
6 ALTER DATABASE tempdb
7 MODIFY FILE (NAME = templog, SIZE=100Mb);
8 GO

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 resize.

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.

Tempdb won’t shrink?

I sometimes get asked about why tempdb won’t shrink when using one of the methods which do not involve a SQL server service restart. Microsoft recommends that if possible, shrinking of tempdb should be done either in single user mode or when there is no current tempdb activity. I have extracted the quote below from the article that is linked to in the previous section.

It is safe to run shrink in tempdb while tempdb activity is ongoing. However, you may encounter other errors such as blocking, deadlocks, and so on that can prevent shrink from completing. Therefore, in order to make sure that a shrink of tempdb will succeed, we recommend that you do this while the server is in single-user mode or when you have stopped all tempdb activity.

– See more at: http://dbadiaries.com/how-to-shrink-tempdb#sthash.QnLyhZjD.dpuf

 

How to move tempdb

How to move tempdb ? So in this post, I show you what is involved to do this and what you shouldn’t try and do.

This is how to move tempdb

You will need to run run some T-SQL and restart the sql server service to complete the operation.

The logical file name values will need to be obtained and there are a couple of ways to do that.

You can either view the logical file names by accessing the database properties and clicking on “Files” or you can run a script. See screen shots below.

how to move tempdb

You can also run this T-SQL:

1 SELECT name, physical_name AS Location
2 FROM sys.master_files
3 WHERE database_id = DB_ID(N'tempdb');
4 GO

On my machine, the results were shown in Management Studio as below and here I am interested in the values in the “name” column. You will need these for the next script.

how to move tempdb

Once you have the logical file names for tempdb, you can run the following script after you have edited it with suitable values for your system. In this example, I am going to set the new path to be another folder on the C: drive of my test server but it is likely that you would be moving tempdb to another drive if this were a real scenario. Whatever path you choose, ensure that the account which the sql server service is running under has permission to read and write to it.

01 USE master;
02 GO
03 ALTER DATABASE tempdb
04 MODIFY FILE
05 (NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf');
06 GO
07 ALTER DATABASE tempdb
08 MODIFY FILE
09 (NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf');
10 GO

Note that the destination path must exist otherwise the script will fail to run.

If the script completes successfully, you will see the following message:

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Now restart your sql server service.

Move tempdb – don’ts

Don’t try and move tempdb using a backup and restore method as you will receive an error

Msg 3147, Level 16, State 3, Line 1
Backup and restore operations are not allowed on database tempdb.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Don’t try and detach tempdb using sp_detach_db @dbname=’tempdb’ as you will again see an error

Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.

I hope you have found this information useful on how to move tempdb and if you would like to watch a video on this topic, you can do so right here.

– See more at: http://dbadiaries.com/how-to-move-tempdb/#sthash.mH6VB1u7.dpuf

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s