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.
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE (‘ALL’)
DBCC FREESESSIONCACHE
DBCC SHRINKFILE (‘tempdev’, 1024)
DBCC SHRINKFILE (‘templog’, 1024)
GO
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).
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_usagewhereuser_objects_alloc_page_count<> 0For 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_objectswhereis_ms_shipped = 0As 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 / 1024FROM sys.database_files;ALTERDATABASEtempdbMODIFYFILE(NAME= 'tempdev', SIZE= <currentdatabasesize+ 1 MB>);