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:
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:
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
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.
In this short post, we learned how to troubleshoot and resolve disk space issues with