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.



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.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of