How to shrink tempdb on active production:

How am I going to shrink tempdb and release space to OS to suppress the disk space alerts on production without restarting SQL Service.

As best practice tempdb is kept locally on production than on SAN disk. This was done  for improving performance. One drawback of this design is that you can’t immediately extend the local drive space. If tempdb drive is full along with data and log files  you need to shrink the files and release the space.

Shrinking tempodb:

Find out if there is any active transaction that is running on tempdb.  Using the system view sys.databases.

select log_reuse_wait_desc,* from sys.databases where database_id = 2

If the log_reuse_wait_desc display “NOTHING”  than you can shrink the tempdb data files and log files using SSMS or DBCC command on tempdb database.


DBCC SHRINKFILE (N’tempdev’ , 1000)

In case there are active transactions and the log_reuse_wait_desc display ” ACTIVE”  you need to find out those active sessions using below query and then KILL it ( only after getting the approval from change management process) and shrink it.


( SELECT s.session_id, [pages] = SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) FROM sys.dm_db_session_space_usage AS sGROUP BY s.session_id HAVING SUM(s.user_objects_alloc_page_count + s.internal_objects_alloc_page_count) > 0 ) SELECT s.session_id, s.[pages], t.[text], [statement] = COALESCE(NULLIF(SUBSTRING( t.[text], r.statement_start_offset / 2,CASE WHEN r.statement_end_offset < r.statement_start_offset THEN 0 ELSE( r.statement_end_offset r.statement_start_offset ) / 2 END ), ), t.[text]) FROM s LEFT OUTER JOIN sys.dm_exec_requests AS rON s.session_id = r.session_id OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t ORDER BY s.[pages] DESC;

If none works out instead of restarting the SQLService and recreating the tempdb with initial size , You can clear the cache by running the below query and shrink the tempdb . please note cleaning cache will have -ve impact on performance till the cache is build up. This step only be tried if restarting SQL Service  is the last option but an outage planning and communication is taking time.

use tempdb go



DBCC FREESYSTEMCACHE (‘ALL’) — clean system cache

DBCC FREESESSIONCACHE — clean session cache

To avoid all these issues , you need to plan tempdb capacity depending on your workload.



Categories: Uncategorised

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts


Transnational replication dependency with MS DTC services

For one of the production environment, we were facing transactional replication latency. Our benchmark to the solution was <=3 hrs where it will reinitialize the subscriber hosted on one of the Public Cloud Datacenter. The Read more…


10 reasons to move to SQL Server 2019

  Read more in the link- Learn more about SQL Server 2019 by downloading the data sheet:


Transaction log full on production due to REPLICATION

Recently we faced a critical issue on production. The transaction log drive was full and the database was not ready to take any new connection from application impacting the site going down. What did we do Read more…