Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Alert for Tempdb Growing Out of Control


By:   |   Read Comments (7)   |   Related Tips: More > System Databases


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


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

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

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


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 Fantastic post Erin, thank you very much !!

  


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

Thanks Erin.

It's usefull for me.


Friday, November 07, 2014 - 3:38:59 PM - Scott Back To Top

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

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 04, 2014 - 10:58:14 AM - James Back To Top

Excellent article and nice code.


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

 

Useful Aricle. Thanks


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

Goodone!!!!!!!


Learn more about SQL Server tools