How Do I shrink TempDB database? | TempDB not shrinking in SQL server

by

This article guides to resolve,

How Do I shrink TempDB database? or empDB not shrinking in SQL server

Temp DB is SQL server system database used for sorting, temp table creation, index maintenance jobs to rebuild index and many more.

Sometime creation of temp table to sort data in large processing tempdb grow huge and get alert for disk space issue.

TempDB database get created when SQL server restart every time. But many cases we will not be able to restart SQL Service in that case

How Do I shrink TempDB database?

But many time you will not be able to shrink file because there are many sessions present on tempdb database or open transactions.

To check open transaction on tempdb run below query:

use tempdb

go

dbcc opentran

If there are no open transaction, then there are data present on buffer cache which causing tempdb not to shrink. 

Run below query to clear data from buffer cache and then run tempdb shrink query. Run below queries one by one and try to run tempdb shrink file.

Note: Please do not run below queries on PROD server without approval.

CHECKPOINT; 

DBCC DROPCLEANBUFFERS; 

GO

DBCC FREEPROCCACHE;

GO

DBCC FREESYSTEMCACHE('ALL');

GO

DBCC FREESESSIONCACHE;

GO

Try to Shrink tempdb database file now.

USE [tempdb]

GO

DBCC SHRINKFILE (N'tempdbfilename' , 0, TRUNCATEONLY)

GO

After running above query some time tempdb file doesn’t get shrunk. Reason is there are few sleeping sessions open on tempdb database which not allowing tempdb to shrink.

How to shrink tempdb files without restart?

Run below query to get sessions present on tempdb which are sleeping. We need to kill this session ID. Post this tempdb can be shrunk.

–This management view can reflect the overall allocation of tempdb space at the time 

SELECT t1.session_id,

t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count,

t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,

t3.*

from sys.dm_db_session_space_usage t1 ,

sys.dm_exec_sessions as t3

where

t1.session_id = t3.session_id

and (t1.internal_objects_alloc_page_count>0

or t1.user_objects_alloc_page_count >0

or t1.internal_objects_dealloc_page_count>0

or t1.user_objects_dealloc_page_count>0)

Once you find all sessions ID, use kill command to remove those sessions which are not allowing to shrink tempdb files. Once those sessions gone you will be able to shrink tempdb file

Script:

USE [tempdb]

GO

DBCC SHRINKFILE (N'tempdbfilename' , 0, TRUNCATEONLY)

GO

Leave a Comment