Recovering from out-of-disk space conditions for tempdb

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