Is it safe to shrink tempdb.mdf in MS SQL Server?

Question

Hej,

my TEMPDB.mdf grew up to 42GB on MS SQL Server 2005 installed on Windows Server 2003.

Last date change is 31/01/2013 at night.

So, I wonder whether this is ok to shrink it?

Solution

I recommend reading the technet article, "Working With tempdb in SQL Server 2005". I don't recommend trying to shrink tempdb since it is used essentially as a scratch db to store intermediate results of queries among other things while SQL is running, and a shrink operation likely will not even work on tempdb. The way to reduce the size of tempdb temporarily is not a shrink operation. Just restart SQL server - SQL will delete tempdb and copy the model database to create a new tempdb. This is the only method I would use to get tempdb temporarily back under control if disk space is an issue.

If tempdb is constantly growing to this size, then you have a db process (or processes) that is working with a lot of data in tempdb at one time causing it to grow. If this is the case, then the only way to prevent it from growing this big in the future is to identify what process(es) it is and change them. If this growth is a one-time deal that you haven't seen before then you can probably just restart SQL server and not have to worry about it again, but if it keeps getting to this size, and you can't determine why or change your backend queries and stored procs to not utilize tempdb as much, then you should probably just set the tempdb size to slightly more than 42 GB to reduce fragmentation caused by autogrowth.

Also read "How to shrink the tempdb database in SQL Server". It details 3 methods to do so. You can do 2 of the 3 without restarting SQL server, but they both require no activity in tempdb which is pretty hard to get on an operational SQL server.

Comments

[joeqwerty]

Reboot the server (or stop and restart SQL Server) and the temp.db database should be deleted and a new empty one created.

[TomTom]

+1. This can be either too little memory (order by etc. overflowing into tempdb) or queries that involve TONS of stuff and - too little memory for them. Especially ETL / DWH load processes handle often hundreds of millions of rows, a hash join on one statement can create a ton of temporary data.