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

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.

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:

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: We need to restart the SQL Server Restart Process, after these changes.