Tempdb is playing more heavily role since SQL Server 2005. if we did not control or monitor it well, it may grow and eventually will fill up the disk.
Tempdb may not be able to shrink if there is an internal object still use tempdb. Per Kalen Delaney suggests in the “Microsoft SQL Server 2008 Internals”, it is better shrink the individual file instead of shrink the tempdb as a whole. She also suggests in the book that it is better to use alter database to change the tempdb size and bounce the MSSQL.
We can use below query to find out What is the current size of the tempdb and its used space.
- Tempdb ‘s db id is always 2
- sys.master_files store the database file last configure size
- Tempdb will be recreated every time base on its last configure size (sys.master_files)
If you do not have luxury for bounce SQL Server. you can try
To remove the tempdb file , we can use
ALTER DATABASE tempdb REMOVE FILE tempdev2
In most case, you will get the error message like "The file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSGU1\MSSQL\DATA\tempdev2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.
After restart the SQL server, the tempdb file is being removed.