Monday, August 13, 2012

Tempdb data file grown high cannot shrink tempdb data file

Today I got the below error:

Could not allocate space for object ' temporary run storage:  111111111111111' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. 

I checked and found that tempdb is grown to occupy all the drive space approx 500GB. which is very 
abnormal. When checked the processes using 
Sp_who2 ‘active’ 
Select * from sys.dm_exec_sessions and  Select * from sys.dm_exec_requests 
Found that big complicated query (several views using several tables join) creating several hash tables 
and temporary objects using tempdb.  

There are several ways to solve this issue:

As tempdb re-creates when we restart the sql server, so restarting the sql server would be 
easiest way to resolve this issue. But generally no one wants to restart the services just due to this, we have an alternative for it but it has its own performance penalty 

DBCC FREEPROCCACHE: as some objects are stored in cached and due to which tempdb holds 
the objects, so clearing the cache would be good option if you are good to pay just clearing 
cache objects, includes compile plan/execution plan and other memory objects –clearing 
memory (which happens when we restart sql services as well). Due to which all the queries will 
be re-compiled and re-executed and slower down the queries output,

This is a great solution this helped me to resolve my tempdb data file growth issue. 


No comments:

Post a Comment