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

10 reasons to move to SQL Server 2019

Read more in the link- Learn more about SQL Server 2019 by downloading the data sheet: http://download.microsoft.com/download/D/2/5/D2519504-0ACD-4CD7-9C34-AB85D5824F34/SQL_Server_2019_Top_10_Reasons_to_Choose_Infographic_EN_US.pdf

Uncategorised

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…

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…

www.000webhost.com