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]
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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s