Today I got the below error:
Could not allocate space for object 'dbo.xxx 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’
And
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.
Ref: http://support.microsoft.com/kb/307487
Could not allocate space for object 'dbo.xxx 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’
And
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.
Ref: http://support.microsoft.com/kb/307487
No comments:
Post a Comment