Alert for Tempdb Growth

By Lori Brown | SQL Administration

May 03

Tempdb is a system database used in SQL Server to hold 1) temporary user objects like temp tables and stored procedures, 2) internal objects like work tables used for sorting, and 3) row versions created from data modification transactions. When configuring a new SQL Server, the tempdb should be set to a size that will accommodate a typical workload for your instance.

Occasionally tempdb files can grow beyond their initial set size, and recently a client contacted us because their tempdb had grown and filled up the entire drive on their system. This happened twice more, and we were not able to identify the cause of the growth while it was happening.

We decided to create an alert that would notify us immediately when the tempdb grows, but also that would record what processes were using the tempdb at the time of the growth. This blog will outline steps to create a table to hold the tempdb usage data, create a SQL job to insert that data into a table, and create an alert to notify and activate the job.

Create table for tempdb usage information

If you don’t already have some type of management database on your instance, you can create a small database as follows:

Next, create a table to hold information about tempdb usage:

Create SQL Agent Job to Insert Data into Table

Use the SSMS GUI to create a new SQL Agent job, and make sure to un-check the Enabled box:

Create the job Step and add the following code in the step:

Leave the job disabled, and do not set a schedule.

Create SQL Agent Alert

Lastly, create a SQL Alert to notify you as soon as tempdb grows past the threshold you stipulate. Using the GUI to create the alert, you need to fill out every field on the General page and make sure the Enabled checkbox is marked. Create a Name for the alerts, then specify the Type as SQL Server performance condition alert. The Object should be Databases, the Counter is Data File(s) Size (KB), and the Instance will be tempdb. The alert will trigger if counter rises above the value. The Value will depend upon the cumulative size of your tempdb files. In this case each tempdb file is 12GB (or 12,288,000 KB), so the total size is 98,304,000 KB.

The Response page is where you will activate the Job you just created.   Mark the Execute Job checkbox and then select the name of the SQL Job you created in the previous step. Also mark the Notify Operators checkbox so that the appropriate person(s) will be notified as soon as the event happens.

On the last page Options, make sure to mark the checkbox for Email so that you will get notified when the alert fires. You can add additional text to the email alert if desired.

This job will not only notify you immediately when growth occurs, but will also activate the job that will insert data into your table so you can investigate what processes were using tempdb at the time.

If you prefer to use TSQL instead of the GUI to create the alert, execute the following script:

Conclusion

Being able to see exactly what is using tempdb space at the time of growth will help you identify heavy tempdb users and check out executions plans for possible tuning. Be sure to truncate your table after you finish troubleshooting so as not to use up unnecessary space.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

  • […] Lori Brown shows how to use a SQL Agent alert to warn you if tempdb grows beyond a certain size: […]

  • >