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
SELECT
name, size
FROM
sys.master_files
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
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 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:
DBCC
SHRINKDATABASE(tempdb, 10);
How to shrink
tempdb using ALTER DATABASE
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.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, SIZE=100Mb);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, SIZE=100Mb);
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 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.
No comments:
Post a Comment