SQL Server Alert for Tempdb Growing Out of Control

Problem

The system database, Tempdb, can appear to have a life of its own. The primary purpose of this database is to temporarily store data for specific processes before being committed to a database. When a Tempdb grows out-of-control it can be a daunting task trying to figure out what is causing the growth. This can be especially problematic when there is little disk space left to work with. One way to stop Tempdb from growing is to restart the SQL services, but one loses all data that might be beneficial in knowing what caused the problem in the first place and who can afford any downtime? How can I get insight into TempDB growth and get notified if there is an issue?

Solution

One option to get notified when TempDB grows is to create a SQL Alert to fire a SQL Agent Job that will automatically send an email alerting the DBA when the Tempdb reaches a specific file size. Let’s look at setting that up and seeing the benefit.

Create the Alert and Job

In SQL Server Management Studio (SSMS), under the SQL Server Agent node, right-click and select New Alert:

On the General Tab, Enter Name: SQL Alert – tempdb Above 3GB (use a value that is conducive to your own environment here)
Select Type: SQL Server performance condition alert
Under Performance condition alert definition: Select Object: SQLServer:Databases

or might just be Databases

Select Counter: Data File(s) Size (KB)
Select Instance: tempdb
Under Alert if counter, Select: rises above
Enter Value: For this sample, I am using 3GB, or 3145728KB, but use a value that fits your needs. For testing you can make this a low value to have alert fire.

Next, go to the Response tab on the left-hand side of the window. Select the checkbox Execute job and click on the New Job… button:

A New Job window will appear:

On the General Tab, Enter Name: DBA – tempdb Above 3GB (use a value that is conducive to your own environment here)
Enter Owner
Make sure the Enabled checkbox is checked.

Next, go to the Steps tab on the left-hand side of the window:

On the General Tab, Enter Step Name: Capture Queries and Send Database Mail
Select Type: Transact-SQL script (T-SQL)
Select Database: tempdb
Enter Command – Copy and paste your edited sample text below into the command text field
Make sure you edit the variables @recipients, @copy_recipients (if applicable), @subject, and @profile_name to be those that suit your environment’s Database Mail profile
Once completed with your edits of the code sample below, click the OK button

Lastly, finish setting up the SQL Alert by going to the Response tab and selecting the newly created SQL Agent Job from the dropdown list and click the OK button:

Below is a sample email results after alert has been triggered, with instructions on how to KILL the session that is growing the Tempdb database:

Code for finding query growing tempdb and emailing it

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST((
  SELECT TOP 5 --Change number accordingly
  su.Session_ID AS 'td','',
  ss.Login_Name AS 'td','', 
  rq.Command AS 'td','',
  su.Task_Alloc AS 'td','',
  su.Task_Dealloc AS 'td','',
 --Find Offending Query Text:
  (SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,
   (CASE WHEN statement_end_offset = -1 
         THEN LEN(CONVERT(nvarchar(max),text)) * 2 
         ELSE statement_end_offset 
   END - rq.statement_start_offset)/2)
  FROM sys.dm_exec_sql_text(sql_handle)) AS 'td'
  FROM      
  (SELECT su.session_id, su.request_id,
   SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,
   SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc
  FROM sys.dm_db_task_space_usage AS su
  GROUP BY session_id, request_id) AS su, 
   sys.dm_exec_sessions AS ss, 
   sys.dm_exec_requests AS rq
  WHERE su.session_id = rq.session_id 
   AND(su.request_id = rq.request_id) 
   AND (ss.session_id = su.session_id)
   AND su.session_id > 50  --sessions 50 and below are system sessions and should not be killed
   AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results
  ORDER BY su.task_alloc DESC  --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth
FOR XML PATH ('tr'), ELEMENTS ) AS NVARCHAR(MAX))
--BODY OF EMAIL - Edit for your environment
SET @body ='<html><H1>Tempdb Large Query</H1>
<body bgcolor=white>The query below with the <u>highest task allocation 
and high task deallocation</u> is most likely growing the tempdb. NOTE: Please <b>do not kill system tasks</b> 
that may be showing up in the table below.
<U>Only kill the query that is being run by a user and has the highest task allocation/deallocation.</U><BR> 
<BR>
To stop the query from running, do the following:<BR>
<BR>
1. Open <b>SQL Server Management Studio</b><BR>
2. <b>Connect to database engine using Windows Authentication</b><BR>
3. Click on <b>"New Query"</b><BR>
4. Type <b>KILL [type session_id number from table below];</b> - It should look something like this:  KILL 537; <BR>
5. Hit the <b>F5</b> button to run the query<BR>
<BR>
This should kill the session/query that is growing the large query.  It will also kick the individual out of the application.<BR>
You have just stopped the growth of the tempdb, without having to restart SQL Services, and have the large-running query available for your review.
<BR>
<BR>
<table border = 2><tr><th>Session_ID</th><th>Login_Name</th><th>Command</th><th>Task_Alloc</th><th>Task_Dealloc</th><th>Query_Text</th></tr>' 
SET @body = @body + @xml +'</table></body></html>'
--Send email to recipients:
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'dba@domain.com', --Insert the TO: email Address here
@copy_recipients ='dba_Manager@domain.com', --Insert the CC: Address here; If multiple addresses, separate them by a comma (,)
@body = @body,@body_format ='HTML',
@importance ='High',
@subject ='THIS IS A TEST', --Provide a subject for the email
@profile_name = 'DatabaseMailProfile' --Database Mail profile here

Next Steps

If you are having issues with TempDB growing out of control consider setting up and testing this tip in your environment.
This was recently tested with SQL Server 2017, so it should work for SQL 2005 through SQL Server 2017.
Check out these resources:

Resources

Community

Subscribe