SQL Server: The TempDB is Full, Shrink it or Move it

In this post, I am providing 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.
It also holds intermediate results that are created during any other query processing.
The TempDB also stores different version of data which are generated using Isolation Levels.

Because of this many reasons TempDB size is increasing so sometimes we require to perform Shrink or we can move TempDB files from one location to another location.

Using this script you can find the correct size of TempDB.

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 size of the TempDB.

Using ALTER DATABASE:

Using SHRINK:


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: SQL Server Restart Process is required after these changes.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz