By: Erin Cook | Comments (9) | Related: More > System Databases
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'[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
- 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:
About the author
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
