Shrink Tempdb without restarting SQL Server

Here we are going to learn different ways of shrinking the Tempdb files

Method 1 :

Monitoring the tempdb system database is an important task in administering any SQL Server environment. From time to time this system database may grow unexpectedly.  Though numerous factors can lead to excessive growth of the tempdb database I have found the most common factor tends to be related to sorting that requires more memory than has been allocated to the SQL Server, which forces the work to be pushed to tempdb.  This can actually be caused by running open queries or even poorly written SSRS reports that allow the user to run the report without limits.  Additionally, any large result sets involving UNION, ORDER BY, GROUP BY, CARTESIAN JOIN, OUTER JOIN, CURSOR, temp tables, table variables or hashing can push work to the tempdb and result in its growth.

The simplest, though not always the most applicable method for getting the tempdb database to shrink is to restart the instance of SQL Server.  However, this may not be an option for many production environments.

Fortunately there is a way to shrink tempdb without taking the server offline.  However, there are some factors that should be considered when applying this method which I have outlined below.  The following script can be used to manage this process.

The above script includes multiple options that can contribute to reducing the size of the tempdb system database. However, you may not need to execute all available commands. Below I have included a brief overview of each command.

DBCC FREEPROCCACHE

Clears out the procedure cache.  This can free up some space in the tempdb but will clear out all cached execution plans, which will need to be rebuild the next time the procedure is called and will require all ad hoc queries and stored procedures to recompile the next time they are executed.  This can result in a temporary performance hit each time a procedure is being called the first couple of times.

DBCC DROPCLEANBUFFERS

Flush cached indexes and data pages.

DBCC FREESYSTEMCACHE (‘ALL’)

Clears the plan cache for the instance of SQL Server.  Clearing the plan cache will cause a recompile of all subsequent execution plans and can result in a temporary decrease of query performance.

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries (queries between servers).

DBCC SHRINKDATABASE(tempdb, 10)

Shrinks the tempdb database by a given percentage.  In this case 10%.  The database can not be made smaller than the minimum size of the database specified when the database was originally created.  The SHRINKDATABASE command can be stopped at any point in the process with all completed work being retained.

To view the current amount of free space in the database run sp_spaceused while connected to tempdb. The first set of output will display the size (in MB) of the current database (both data and log files) as well as space in the database that has not been reserved for database objects (unallocated space).

tempdb_sp_spaceused_output

The second set of output will display the total amount of space allocated by objects in the database (reserved), total amount of space used by data (data), total space used by indexes (index_size) and the total amount of space reserved for objects in the database that has not been used (unused).

DBCC SHRINKFILE (‘tempdev’) 
DBCC SHRINKFILE (‘templog’)

Shrinks the size of the specified data or log file for the current database.  Make sure to include USE [tempdb] or manually specify the database in Management Studio prior to execution.  The SHRINKFILEoperation can be stopped at any point in the process with all completed work being retained.

In addition to tempdev and templog, depending on your instance of SQL Server, you may have additional tempdb files that need to be included.  If so, simply replicate the above SHRINKFILE command and include each additional tempdb file.

Make sure you do not have any open transactions when running a SHRINKFILE command. Open transactions may cause the operation to fail and could potentially corrupt the tempdb system database.  Though this is certainly a worst case scenario, its a pretty bad one so just use some caution.

It is best to fully understand these options before taking any action to shrink your tempdb system database. However, if tempdb is growing and nearing the point where there is no available space and restarting the instance of SQL Server is not an option, the steps outlined above are likely your best bet for getting the size down to a more manageable number.

Method 2

Sometimes below procedure may work :

Execute below command :

DBCC SHRINKFILE ('tempdev', 1024)

The query executed successfully but the size of the database did not change.

The next obvious step would be to check for any open transaction on tempdb.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

or

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

No locks! Then I stumbled upon this article on MSDN. There was a mention of sys.dm_db_session_space_usage DMV which helps to  track the number of page allocation and deallocation by each session on the instance. Hence tried my luck with this query.

select* fromsys.dm_db_session_space_usage
whereuser_objects_alloc_page_count<> 0
The output was a pleasant surprise.

For me the last two session ids were of the DBA trying to shrink the database. The first one was of one of the application which was in sleeping status. Thinking that I found the culprit, I asked my team to reach out the Application team to check if this session can be killed. Since I was eager to catch some precious Friday night sleep, told my team “Guys, kill the SPID and then shrink it. Everything should be fine.” Without a second thought logged from the server and slept off peacefully.

Welcome Saturday. Since I did not get any calls overnight, I just peeped into see if the issue was resolved. No! “Pradeep, we deleted one old backup file on the drive and now the drive has some free space left. But the tempdb is not shrinking yet” was the response from my team when I called up. Wow! This issue was getting very interesting.

Any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there on it.

SELECT* FROMtempdb..sys.all_objects
whereis_ms_shipped = 0
The is_ms_shipped column would be 1 for all the system objects. The output of this query looked strange.

As guessed, there were plenty of user tables on tempdb. But why are they here? Most likely some user executed a stored procedure which made the tempdb grow and eventually that session got terminated because tempdb ran out of space. Most importantly when a stored procedure is run cached objects are created on tempdb. These cached objects are in turn associated with a query plan. These objects are still present in tempdb because the query plan of the run away query is still present in the Procedure Cache. By design, these cached tables are not deleted, instead they are truncated so that these tables can be reused when the stored procedure is executed again. This article has more details on this topic.

The only way to get rid of these cached objects is to clear the Procedure Cache. It is not a good idea to clear the procedure cache, since it will force the stored procedures to be recompiled and thereby negatively affecting performance. But for me, tempdb size was more of a concern than the stored procedures being recompiled. I went ahead and cleared the Procedure Cache using the following command.

DBCC FREEPROCCACHE

DBCC SHRINKFILE now did what it was supposed to do.

Tempdb was finally shrunk!

 

Method 3 :

In some cases, rebooting the server is not an option, and should be always the last option, you can trick it (reinitialize) by increasing the data file size (e.g. 1 MB).

USE tempdb;
SELECT
 name,
 size * 8 / 1024
FROM sys.database_files;
Find out the size of the database and add 1 MB.
ALTERDATABASEtempdb
MODIFYFILE
(NAME= 'tempdev', SIZE= <currentdatabasesize+ 1 MB>);
After execute the command, DBCC SHRINKFILE should works again without any error message.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.