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.

;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.

 

 

Categories: Uncategorised

Leave a Reply

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

Related Posts

Uncategorised

Transcational Replication fails with -“Could not find stored procedure ‘sp_MSins_.’

We have SQLServer 2014,transactional  replication on Azure ARM VM. This started failing with the error Transactional Replication to Pull from the Publisher. I did not choose to update schema or do a Snapshot. And now Read more…

Uncategorised

Adding new tables to existing Replication

Sometime due to the business requirement you might need to add tables to an existing transactional replication. This might be expensive activity if you add new tables specifically to an existing replication which is having Read more…

Uncategorised

SQL SERVER – The Patch Installer has Failed to Update the Shared Features

Applying a patch in SQL Server is a planned process which is followed across various companies. Most of the companies take time to apply the patch to make sure the stability of the patch can Read more…

www.000webhost.com