We got a critical drive error alert(we have our own alerting tool) for the drives which contains Tempdb.
2011-10-13 04:51:31.500 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.
When checked found that tempdb is grown to occupy all the drive space appx 500GB. which is very abnormal. When checked the processes using
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.
As tempdb is always in “Simple” recovery model, we cannot take backup and shrink data/log if its in using.
More about tempdb is blogged here.
In this case when you see the shrink data file it will show available space would be in “Negative” and could not able to shrink it. because the hash objects are occupying the space on tempdb.
*It could be possible that we have restricted the growth or all the disk space is occupied.
Data file contains data.
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
sqlservr -c -f
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. Remember if you are okay with the performance you can try this.
Hope this helps.
Tomorrow another blog for troubleshooting hope this series will help all our supporting DBA to solve their production related issues.