Here we are going to learn different ways of shrinking the Tempdb files
Method 1 :
In every SQL Server installation, keeping an eye on the tempdb system database is critical. This system database may develop unexpectedly from time to time. Though there are a number of variables that can cause the tempdb database to grow too large, I’ve observed that the most typical cause is sorting, which requires more RAM than the SQL Server has allocated, This causes the work to be moved to tempdb. Running open queries or even badly constructed SSRS reports that allow the user to run the report without limits can cause this. Moreover, any large result sets containing UNION, ORDER BY, GROUP BY, CARTESIAN JOIN, OUTER JOIN, CURSOR, temp tables, table variables, or hashing can cause work to be pushed to the tempdb, causing it to grow.
The easiest, though not usually the most effective, technique for shrinking the tempdb database is to restart the SQL Server instance. However, in many production environments, this may not be an option.
Fortunately, shrinking tempdb without taking the server offline is possible.
However, there are a few things to keep in mind when using this strategy, which I’ve listed below.
USE [tempdb]
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE ('ALL')
DBCC FREESESSIONCACHE
DBCC SHRINKFILE ('tempdev', 1024)
DBCC SHRINKFILE ('templog', 1024)
GOThe script above includes a number of parameters that can help reduce the size of the Tempdb system database. However, you may not need to use all of the commands offered. I’ve given a quick summary of each command below..
DBCC FREEPROCCACHE
This command will clear procedure cache and this will free up some space in the Tempdb, but it will also clear away all cached execution plans, which will need to be rebuilt the next time the procedure is run, as well as requiring all ad hoc queries and stored procedures to be recompiled. This can cause a brief performance hit the first few of times a procedure is executed.
DBCC DROPCLEANBUFFERS
This command Flushes cached indexes and data pages.
DBCC FREESYSTEMCACHE (‘ALL’)
This command Clears out the plan cache for SQL Server instance. Clearing the plan cache will require all subsequent execution plans to be recompiled, which may result in a temporary drop in query performance.
DBCC FREESESSIONCACHE
The distributed query connection cache, which is used by distributed queries, is flushed (queries between servers).
DBCC SHRINKDATABASE(tempdb, 10)
Reduces the size of the tempdb database by a specified percentage. In this situation, the percentage is 10%. The database can’t be made any lower than the minimum size specified when it was built. All finished work is kept if the SHRINKDATABASE command is interrupted at any time throughout the procedure.
While connected to tempdb, run sp spaceused to see the current amount of free space in the database. The first set of output will show the current database’s size (in MB) (including data and log files), as well as database space that has not been reserved for database objects (unallocated space).
The second set of output will show the total amount of space reserved for database objects that have not been used (reserved), the total amount of space used by data (data), the total amount of space used by indexes (index size), and the total amount of space reserved for database objects that have not been used (unused).
DBCC SHRINKFILE (‘tempdev’)
DBCC SHRINKFILE (‘templog’)
Reduces the size of the current database’s selected data or log file. Prior to execution, make sure to include USE [tempdb] or manually specify the database in Management Studio. The SHRINKFILE operation can be halted at any time during the process, and all finished work will be saved.
You may need to provide extra tempdb files in addition to tempdev and templog, depending on your SQL Server instance. If this is the case, simply repeat the SHRINKFILE command above, adding each subsequent tempdb file.
When using the SHRINKFILE command, be sure there are no open transactions. Open transactions may cause the operation to fail, and the tempdb system database may be corrupted as a result. Though this is a worst-case situation, it’s still a bad one, so proceed with caution.
Before you take any action to minimise your tempdb system database, be sure you understand all of your alternatives. If tempdb is increasing to the point where there is no more space available and restarting SQL Server isn’t an option, the techniques indicated above are probably your best hope for reducing the size to a more reasonable quantity.
Method 2 :
The following approach may work in some cases:
Run the following command:
DBCC SHRINKFILE ('tempdev', 1024)The query was successful, however the database size remained same.
The next obvious progression would be to check tempdb for any open transactions.
SELECT * FROM sys.dm_exec_requests WHERE database_id = 2
OR
SELECT * FROM sys.dm_exec_requests WHERE database_id = 2There are no locks! Then I came upon this article on MSDN. The sys.dm db session space usage DMV was mentioned, which may be used to track the amount of page allocations and deallocations made by each session on the instance. As a result, I gave this query a shot.
select* fromsys.dm_db_session_space_usage
where user_objects_alloc_page_count<> 0The result was pleasantly surprising.
The DBA was attempting to shrink the database in the last two session ids for me. The first was of one of the applications that was in the process of sleeping. I advised my team to contact the Application team to see whether this session could be terminated, believing I had located the offender. “Guys, kill the SPID and then shrink it,” I told my colleagues, anxious to get some much-needed Friday night sleep.
Saturday is here. I simply checked to see if the problem had been rectified because I hadn’t received any calls overnight. No! , we removed one of the previous backup files from the drive, and now it has some empty space. When I called up, my team responded, “But the tempdb isn’t reducing yet.” Wow! This was becoming a fascinating topic.
Tempdb would be used by any session to create temporary objects. Because the Tempdb database was not reducing, it was inevitable that some user-defined tables will be present.
SELECT * FROM tempdb..sys.all_objects
where is_ms_shipped = 0For all system objects, the is ms shipped column would be 1. This query returned a surprising result.
On tempdb, there were a lot of user tables, as expected. But why are they here in the first place? Most likely, some user launched a stored procedure that caused the tempdb to grow, and the session was eventually ended because the tempdb ran out of space. Most significantly, when a stored procedure is executed, tempdb cached objects are created. A query plan is linked with these cached objects.
Because the query plan of the runaway query is still stored in the Procedure Cache, these items are still present in tempdb. These cached tables are not removed by default; instead, they are trimmed so that they can be reused when the stored procedure is called again.
Clearing the Procedure Cache is the sole way to get rid of these cached objects. Clearing the procedure cache is not a good idea since it forces the stored procedures to be recompiled, which has a detrimental impact on performance. However, the size of the tempdb was more of an issue for me than the recompiled stored procedures. I proceeded to clear the Procedure Cache using the steps below.
DBCC FREEPROCCACHEDBCC SHRINKFILE had finally completed its task.
Tempdb was finally reduced in size!
Method 3:
If rebooting the server is not an option and should always be the last resort, you can mislead it (reinitialize) by increasing the size of the data file (e.g. 1 MB).
USE tempdb;
SELECT
name,
size * 8 / 1024
FROM sys.database_files;Calculate the database’s size and add 1 MB.
ALTER DATABASE tempdb
MODIFYFILE
(NAME= 'tempdev', SIZE= <currentdatabasesize+ 1 MB>);After running the command, DBCC SHRINKFILE should function normally again, with no errors.