SQL Server 2005 - how to shrink the tempdb

Question

Hi,

My tempdb size is growing too much, as we are doing too many transactions in the database. Can someone let me know how to shrink this tempdb without restarting the server. I have tried DBCC SHRINKDATABASE and it didn't help

Thanks in advance

Answer 1

Shrink the data files. From tempdb:

dbcc shrinkfile ('tempdev')

dbcc shrinkfile ('templog')

If you have other data files for tempdb you may need to shrink them also. You can see this from the system data dictionary. Look in the 'name' column.

select * from sys.database_files

Answer 2

Your tempdb is the size it needs to be...

It will just grow again, just even more fragmented on disk and possibly blocking while it grows too.

More seriously, I'd set the size of tempdb to soemthing sensible and slightly larger than now, shut down SQL, delete the old files to be 110% sure, start SQL Server up again...

Answer 3

I have found shrinking the tempdb to often be a troublesome task.

The solution I have used previously is to set the initial size of the tempdb database to be the actual desired size of the database. Restarting the SQL Server Service will then re-create the tempdb database to this sepcified size.

You may also find the following Microsoft reference to be useful reading:

http://support.microsoft.com/kb/307487/en-gb