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.

Viewing tempdb space usage

The following dynamic management views (DMVs) can be used to report information about tempdb space:

• sys.dm_db_session_space_usage – Retrieves the number of pages allocated and deallocated by each session for the database.
• sys.dm_db_task_space_usage – Retrieves the page allocation and deallocation activity by task for the database.
• sys.dm_db_file_space_usage – Retrieves information about disk space used by any database.

How to move tempdb?

SQL Server creates the tempdb database every time it starts up. Therefore, you only need to change the pointer to move the tempdb database and log files to move it to a new location. To do this:

• Retrieve the logical name of the database and log files from the sys.master_files catalog.
• Run ALTER DATABASE with the MODIFY FILE option to specify the new locations for the tempdb database data and log files (see below):

  USE [master]
  GO
  ALTER DATABASE tempdb 
  MODIFY FILE (name = tempdev, filename = N'<Specify_New_OS_Path>\tempdb.mdf')
  GO
  ALTER DATABASE tempdb 
  MODIFY FILE (name = templog, filename = N'<Specify_New_OS_Path>\templog.ldf')
  GO

  Once complete, an informative message will appear stating that tempdb will be moved the next time SQL Server is started (see below):

  The file "tempdev" has been modified in the system catalog. 
  The new path will be used the next time the database is started.
  The file "templog" has been modified in the system catalog. 
  The new path will be used the next time the database is started.

• Restart the SQL Server service. Don’t forget to delete the files from old location.

In this short post, we learned how to troubleshoot and resolve disk space issues with tempdb.