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

Replication using Remote distributor on ALWAYS ON AVAILABILITY GROUP

In SQL Server, Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. It is highly recommended to Read more…

Uncategorised

Configuring transnational replication on Always on availability group SQL Server 2014

SQL Server replication supports the automatic failover of the publisher, the automatic failover of transactional subscribers, and the manual failover of merge subscribers. The failover of a distributor on an availability database is not supported. In Read more…

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…

000webhost logo