Day 1: Tempdb data file grown high cannot shrink tempdb data file.(available size in – negative)

Description:-

We got a critical drive error alert(we have our own alerting tool) for the drives which contains Tempdb.

Got error:

 

  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

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.

Observation:-

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.

Resolution:-

There are several ways to solve this issue

  1.  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

  1. 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.

Reference:

Advertisements
This entry was posted in TempDB and tagged , . Bookmark the permalink.

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