SQL Server Alert for Tempdb Growing Out of Control

By:   |   Comments (9)   |   Related: More > System Databases


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?


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.
Set Up New Alert - General Tab

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:

Set Up New Alert - Response Tab

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.
Set Up New Job - General Tab

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
Set Up New Job Step - General Tab

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:

Set Up Alert to Execute Job

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:

Sample Email

Code for finding query growing tempdb and emailing it

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'
  (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
--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> 
To stop the query from running, do the following:<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>
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.
<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'[email protected]', --Insert the TO: email Address here
@copy_recipients ='[email protected]', --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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Erin Cook Erin Youtzy has been dabbling in SQL at the workplace since she earned her BS degree in 2004. She is currently a DBA at UNM.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 6, 2019 - 3:09:51 PM - Greg Robidoux Back To Top (78979)

Hi smt,

the returned line you are getting in the email is from Database Mail.  Run a process that inserts a lot of data into a Temp table and you should see this in the email list.  I did some tests and I had the same result until I did a large insert into a temp table.


Friday, January 25, 2019 - 11:33:15 AM - smt Back To Top (78884)

 I simulated Tempdb size by inserting some rows using session_id 61 but I get email like this:

64 (session_id 64 is wrong) --- userABCD (userName is right) ----DELETE00WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeou ( this is not the Text, it was "insert into  #mytemp values ( 1, 'absdfjalkfjasd')"

can you please help?

Monday, January 25, 2016 - 10:42:46 AM - Joseph Fallon Back To Top (40493)

 Fantastic post Erin, thank you very much !!


Friday, February 27, 2015 - 2:34:54 AM - Hussein Patel Back To Top (36371)

Thanks Erin.

It's usefull for me.

Friday, November 7, 2014 - 3:38:59 PM - Scott Back To Top (35225)

This was an excellent article. I plan to use this on one of our more scrutinized server immediately.


Well Done!

Wednesday, October 22, 2014 - 1:12:24 PM - Lynn Nguyen Back To Top (35038)

I follow those steps to set up the Alert but why the Alert still fired even though the tempdb file size does not rise (not even change in size) above the Alert condition?

Monday, August 4, 2014 - 10:58:14 AM - James Back To Top (33993)

Excellent article and nice code.

Monday, July 21, 2014 - 4:40:57 PM - Srini Back To Top (32808)


Useful Aricle. Thanks

Monday, July 21, 2014 - 1:19:38 PM - Sri Back To Top (32805)


get free sql tips
agree to terms