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.
- Rebuilding an index with
- 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.
tempdb space usage
The following dynamic management views (DMVs) can be used to report information about
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
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
ALTER DATABASEwith the
MODIFY FILEoption to specify the new locations for the
tempdbdatabase 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
tempdbwill 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