Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.
Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.
Tempdb stores temporary tables as well as a lot of temporary (cached) information used to speed up queries and stored procedures. For the best chances in shrinking tempdb, we’re going to clear these different caches (except for the temp tables, which you should drop manually).
First off, the easy way out
It’s worth mentioning. If you’re not running a production-like environment, your best bet is to restart the SQL Server service. This will return tempdb to its default size, and you won’t have to worry about all the potential pitfalls of this article. But since you’re reading this, chances are you can’t just restart the server. So here goes:
Warning: These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server. Don’t do this stuff unless absolutely neccessary.
DBCC DROPCLEANBUFFERS
Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.
CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO
DBCC FREEPROCCACHE
Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.
DBCC FREEPROCCACHE; GO
DBCC FREESYSTEMCACHE
This operation is similar to FREEPROCCACHE, except it affects other types of caches.
DBCC FREESYSTEMCACHE ('ALL');
GO
DBCC FREESESSIONCACHE
Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.
DBCC FREESESSIONCACHE; GO
.. and finally, DBCC SHRINKFILE
DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.
Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!
DBCC SHRINKFILE (TEMPDEV, 20480); --- New file size in MB
GO
Don’t set the new size too low! Make a realistic estimate of the largest “normal” size that tempdb will assume during normal day-to-day operation.
That’s it. If everything works the way it should, you should now be able to verify the new size of tempdb.
A word about shrinking database files
Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation on your physical storage medium. This is because the sectors that the file used to occupy may now very well be occupied by other information (just a few bytes are enough). When SQL Server wants to grow that database file, the newly added portion of the file will need to be placed elsewhere on the disk, thus creating fragmentation.
The number one mortal sin in this context is “autoshrink“, because it may very well add to the drive fragmentation every time it runs, which could be very frequently.
As a rule of thumb, never ever autoshrink a database. And try to be very restrictive when it comes to shrinking databases or files in general, unless it’s a one-off operation to fix the aftermath of a runaway query.
Tack för tipset! Välbehövligt 🙂
Thanks for your great explanation. It works perfect!
can we shrink temp db frequently..
No, like the post says, “Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation”
Hi, this works like a charm, but i still have a doubt, How does the below queries helps in shrinking the tempdb file when these cache’s exist in the memory?. Or am i wrong. How are these caches and TEMPDB related?
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE
You’d have to google the specifics, but generally speaking these buffers and caches can allocate soace in tempdb and prevent it from shrinking.
Remember that a single log record or page in tempdb could hold up the shrink. Therefore the brute force approach.
Hi Daniel,
Could you please confirm, How to find that holding page file in tempdb that causing to stop shrink db. please advise me.
I’m afraid that finding a specific page that is blocking tempdb from shrinking is a bit out of my depth. Also, there’s a difference between how a database (the .mdf files) and the log (the .ldf files) shrinks.
Your best bet is to read up on this article: https://msdn.microsoft.com/en-us/library/ms189493.aspx
You should also check for open transactions to identify what process might be holding an open transaction. Try executing “sp_who2”, or try my “sessions and locks” script, which is available from the download page.
Thanks Dan,
It worked well for me. I have been trying the various DBCC shrink commands but unable to shrink it other than restarting the SQL instance.
Hi Daniel,
I have a requirement to delete 10 million records in some set of tables. However on deleting some set of records, noticed that Database Log file is growing too fast. Have tried restarting SQL Server but still database Log size didn’t reset…However only tempdb size reset… Is there a way to automate the Shrinking Process on SQL Server Restart ? Please suggest.
Thanks & Regards,
Pradeep
Sometimes you need to change some stuff first These commands worked for me, especially after I accidentally set max connections to 1 in the process of “fixing” temp db:
NET START “SQL Server (SQLEXPRESS)” /m”SQLCMD”
sqlcmd -S .\SQLEXPRESS
EXECUTE sys.sp_configure
@configname = ‘show advanced options’,
@configvalue = 1;
RECONFIGURE;
EXECUTE sys.sp_configure
@configname = ‘user connections’,
@configvalue = 0;
RECONFIGURE;
use tempdb
dbcc shrinkfile (tempdev, 5)
dbcc shrinkfile (templog, 5)
go
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files
I can’t see why setting the maximum number of user connections to 0 (infinite) would change anything with regards to shrinking tempdb. Could you elaborate on why you would do that?
I made the mistake in thinking that all I had to do to start the server in “single connection mode” was to reduce the number of maximum connections to 1, and restart the server. That was the wrong approach.
If anyone makes that mistake going forward, they just have to refer to the script I put there to fix both problems at once.
Doesn’t work for me. Here’s the query I ran:
DBCC DROPCLEANBUFFERS
GO
dbcc FREESYSTEMCACHE (‘ALL’)
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE;
GO
USE tempdb;
GO
DBCC SHRINKFILE(‘tempdev2’, EMPTYFILE)
GO
ALTER DATABASE tempdb
REMOVE FILE tempdev2
GO
And the output:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHRINKFILE: Page 3:406 could not be moved because it is a work table page.
Msg 2555, Level 16, State 1, Line 12
Cannot move all contents of file “tempdev2” to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5042, Level 16, State 1, Line 14
The file ‘tempdev2’ cannot be removed because it is not empty.
———
Also, I made sure no other sessions (other than my own) are using tempdb with “sp_who2”. Any other suggestions?
Thanks,
Brian
Try my suggestion, which works through re-starting the server in protected mode as well.
Isn’t the entire point of this article how to do this shrink without restarting the service?
Brian, you might also consider not emptying the file completely. Try leaving a few megabytes in.
It helps me to reduce tempdb size from 57 GB to 30GB as my Disk space went in MBs.
I also created one more file to manage load. but location is not same disk.
Disks are SSD.
Hope this solution resolve my issue of disk space
I am unable to shrink the tempdb database , no active transaction found
all files SQL files kept on single C: drive, only 16 GB disk space is free on C drive,
Msg 2556, Level 16, State 1, Line 11
There is insufficient space in the filegroup to complete the emptyfile operation.