13 January,2011 by Jack Vamvas
Despite best efforts to size a tempdb ,sometimes the tempdb expands to a size as big as the drive. Because the drive is full the tempdb can no longer expand. This triggers a situation in a monitoring framework. A quick solution is required, to shrink down the tempdb . Following are 2 different methods I use , that don’t require a SQL Server restart.
It is important to focus on the reason for continious TEMDB growth , as the problem will just reappear
If I receive consistency errors , than I resort to a SQL Server restart , depending on the situation
DBCC DHRINKFILE will allow you to decrease to a size smaller than the original size
Use tempdb GO --shrink the main data file DBCC shrinkfile (tempdev,’) GO --shrink the main log file DBCC shrinkfile (templog, ’) ---e.g DBCC shrinkfile (tempdev, 5000)
DBCC SHRINKDATABASE(tempdb,’’) --e.g DBCC SHRINKDATABASE(tempdb,20)