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.
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.
;WITH s AS
( 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 FREEPROCCACHE — clean cache
DBCC DROPCLEANBUFFERS — clean buffers
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.