SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Viewing tempdb space usage

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

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]
    ALTER DATABASE tempdb 
    MODIFY FILE (name = tempdev, filename = N'<Specify_New_OS_Path>\tempdb.mdf')
    ALTER DATABASE tempdb 
    MODIFY FILE (name = templog, filename = N'<Specify_New_OS_Path>\templog.ldf')

    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.

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.


Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...