Shrinking the tempdb in SQL Server

The tempdb is (as the name suggests) a temporary database that is re-created every time the SQL server instance is restarted.

It is typically used to store global and local temporary tables, temporary sotred procedures and so on.

The are some also some major restrictions (or differenes) to standard SQL databases:

- The tempdb can't be dropped.
- You are not able to perform a backup on it.
- You are unable to change it's recovery model.

It is usually a good idea to ensure that the tempdb has it's own separate partition. By default the database is 8MB in size (with a log file size of 1MB_ and autogrowth is enabled (at 10%.)

In some scenerios it might be necessary to shrink the tempdb - we have two options:

- To do this without restarting SQL server we can do the following:

** Warning: Performing the following will (to some degree) temporarily degrade performance of the SQL server - read below for more information. **

1. Clear the proecdure cache with FREEPROCCACHE. By doing this all queries and stored procedures that were cached will have to be recompiled - hence degrading performance:

DBCC FREEPROCCACHE;
GO

2. The DROPCLEANBUFFERS will clear cached indexes and data pages:

DBCC DROPCLEANBUFFERS;
GO

3. The FREESYSTEMCACHE command will clear unused cached entries for the system:

DBCC FREESYSTEMCACHE ('ALL');
GO

4. The FREESESSIONCACHE command clears session cache data between SQL servers.

DBCC FREESESSIONCACHE;
GO

5. ** Before performing the following ensure that there are no open transaction to the tempdb before shrinking it as it could corrupt the DB!

DBCC SHRINKFILE (TEMPDEV, 1024); (Size in MB)
GO