Another potential problem that you need to tackle as a DBA is running out of disk space on the hard disk that contains tempdb.This is because SQL Server makes extensive use of tempdb when:

• Tracking versions for row-versioning concurrency.
• Performing bulk load operations on tables with triggers enabled.
• Running DBCC CHECKDB.
• Rebuilding an index with SORT_IN_TEMPDB option.
• Variables of LOB data types.
• Storing intermediate query results, for example, during joins, aggregates, or sorts.
• Service broker dialog information.
• Caching temporary objects and tables.
• Storing inserted and deleted tables in triggers.
• Running sp_xml_preparedocument.

Continue reading →