Unable to Shrink Tempdb Data and Log files


Unable to Shrink Tempdb Data files have grown large.

You may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty

In any case we can restart SQL instance to re-initialize tempdb.

However, its not easy to restart for critical application databases.

Restarting the SQL services is very difficult sometime,

last option that can be tried that you can reinitialize by increasing the data file size to 1 MB and then start shrink activity.

 


USE tempdb;

SELECT
 name,
 size * 8 / 1024
 FROM sys.database_files;


--Find out the size of the data files and add 1 MB.


ALTER DATABASE tempdb
 MODIFY FILE
 (NAME = 'tempdev', SIZE = <current database size + 1 MB>);



ALTER DATABASE tempdb
 MODIFY FILE
 (NAME = 'tempdev2', SIZE = <current database size + 1 MB>);


--After execute the command, DBCC SHRINKFILE should works again without any error message.


DBCC SHRINKFILE ('tempdev', 1024)


Still unable to shrink…?

The next obvious step would be to check for any open transaction on tempdb.

SELECT * FROM sys.dm_exec_requests WHERE database_id = 2

No open transactions! Alright, any process holding locks on tempdb?

select * from sys.dm_tran_locks
where resource_database_id= 2

No locks! There is sys.dm_db_session_space_usage DMV which helps to track the number of page allocation and deallocation by each session on the instance.

select * from sys.dm_db_session_space_usage
where user_objects_alloc_page_count<> 0

We can clear the user sleeping session in case we see something here.
The another key thing is, any session would use tempdb for creating some temporary objects. Since the database was not shrinking, obviously some user defined tables would be there on it. Execute this…

SELECT * FROM tempdb..sys.all_objects where is_ms_shipped = 0

The is_ms_shipped column would be 1 for all the system objects. Hence, we can get the detail of user objects temporarily created…

In case we get objects list with #tablename in name column. We need to run below.

DBCC FREEPROCCACHE

Note: Its not recommended to clear the procedure cache, it will force the SPROCs to be recompiled and thereby effects performance. Anyhow, If tempdb is more of a concern than the stored procedures being recompiled. We can go ahead and clear the Procedure Cache using the above mentioned DBCC.

 

Still if there is no luck

Finally

You can also use DBCC FREESYSTEMCACHE (‘ALL’) to clear the plan cache for the SQL server instance, but it would result decrease in the query performance temporarily.

DBCC FREESYSTEMCACHE (‘ALL’)
GO

You can also use DBCC FREESESSIONCACHE to flush the distributed query connection cache used by distributed queries against the SQL server instance.

DBCC FREESESSIONCACHE
GO
After execute the command, DBCC SHRINKFILE should works again without any error message.

DBCC SHRINKFILE (‘tempdev’, 1024)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: