How to monitor the tempdb space

Tempdb is a temporary area where SQL Server. SQL Server uses tempdb for:

  • Bulk operations
  • Common table expression queries
  • Cursors
  • Event notification
  • Indexes/sort_in_tempdb
  • Very large object (varchar,text, image...)
  • Row versioning
  • Triggers
  • XML

tempdb must be large enough for all these operations.

tempdb space

  1. SELECT CAST(SUM(user_object_reserved_page_count)*8 AS VARCHAR(16)) + ' Kb' AS 'User Objects',
  2.            CAST(SUM(internal_object_reserved_page_count)*8 AS VARCHAR(16)) + ' Kb' AS 'Internal Objects',
  3.        CAST(SUM(version_store_reserved_page_count)*8 AS VARCHAR(16)) + ' Kb' AS 'Version storage',
  4.        CAST(SUM(unallocated_extent_page_count)*8 AS VARCHAR(16)) + ' Kb' AS 'Freespace'
  5. FROM sys.dm_db_file_space_usage
  6. WHERE database_id = 2;
  7. go
User Objects        Internal Objects    Version storage     Freespace
------------------- ------------------- ------------------- -------------------
256 Kb              0 Kb                0 Kb                6976 Kb

(1 row(s) affected)