This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to Shrink TempDB when it is full or occupying more hard disk space.
We know that TempDB contains temporary tables and other objects which are created by the user or system.
It also holds intermediate results that produced during query processing.
The TempDB also stores the different version of data which are generated using Snapshot Isolation Levels.
Because of above all reasons TempDB size is increasing so sometimes, we need to perform Shrink or we can move TempDB files from one location to another location.
Using below script you can find the correct size of TempDB.
1 2 3 4 5 6 7 8 9 10 11 |
USE TempDB GO SELECT Name ,Physical_Name ,Type_Desc ,(Size*8) AS SizeInKB FROM SYS.DATABASE_FILES GO |
If we want to reset TempDB at some configured size, we should use to Shrink the TempDB.
We can also use both ALTER DATABASE and SHRINK command to reduce the size of the TempDB.
Using ALTER DATABASE:
1 2 3 4 5 6 7 |
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB) --Desired target size for the data file ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB) --Desired target size for the log file |
Using SHRINK:
1 2 |
dbcc shrinkdatabase (tempdb, 'target percent') -- This command shrinks the tempdb database |
Move TempDB from one location to another location:
If we are facing size problem for a particular drive, we can also move the TempDB from one drive to another drive.
Before moving a TempDB, we should make sure that is set to autogrow and check the original size of TempDB files because we also require enough more space in the new location.
Note: We need to restart the SQL Server Restart Process, after these changes.
1 2 3 4 5 6 7 8 9 10 |
USE MASTER GO ---- change the location of tempdev file ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'f:tempdb.mdf') GO ---- change the location of templog file ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'f:templog.ldf') GO |