What to do when TEMPDB is full - in SQL 2000 & 2005 versions
My TEMPDB is full what to do?
This is a very common question in the forums and newsgroup, the first and foremost thing to check is to ensure TEMPDB drive has got enough free space to accomodate the calculations that are involved with the queries.
This ASPFAQ link is very very useful for the users to take care and prevent such situation within their environment. Bear in mind though it talks most of SQL 2000 version it is still applicable to SQL 2005 version. There are other factors to consider about the TEMPDB enhancement in SQL 2005 version. They are:
- Within 2005 certain changes to records in tempdb are no longer logged, as a way to reduce the amount of I/O traffic for tempdb, its log and the devices both are hosted on. INSERT actions only log what something has been changed to, and DELETE actions only log what was deleted. Only UPDATE actions log both, the original and changed data.
- Dynamic Management Views report back statistics about tempdb's space usage, which can be retrieved with a query. For instance, SELECT SUM (unallocated_extent_page_count)*8 as [Free Space] FROM sys.dm_db_file_space_usage would return the total amount of free space in kilobytes for tempdb's files.
- Two new performance counters, Temp Tables Creation Rate and Temp Tables for Destruction, are now available for SQL Server 2005.
Working with TEMPDB in SQL 2005 technet whitepaper link.
Trouble shooting the performance in SQL 2005 - tempdb relation link.