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:

--create a small database 
CREATE DATABASE [ManagementDB]
 ON   
( NAME = N'ManagementDB', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ManagementDB.mdf' , 
SIZE = 102400KB , 
FILEGROWTH = 10240KB )
 LOG ON 
( NAME = N'ManagementDB_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ManagementDB_log.ldf' , 
SIZE = 10240KB , 
FILEGROWTH = 10240KB )
GO

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

--create table in your management database
USE ManagementDB
GO
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.TempdbGrowth
	(
	session_id smallint NOT NULL,
	request_id int NULL,
	task_alloc_GB numeric(18, 0) NULL,
	task_dealloc_GB numeric(18, 0) NULL,
	host nvarchar(50) NULL,
	login_name nvarchar(128) NULL,
	status nvarchar(30) NULL,
	last_request_start_time datetime NULL,
	last_request_end_time datetime NULL,
	row_count bigint NULL,
	transaction_isolation_level smallint NULL,
	query_text nvarchar(MAX) NULL,
	query_plan xml NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.TempdbGrowth SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

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:

USE ManagementDB
GO
INSERT INTO TempdbGrowth
(session_id, request_id, task_alloc_GB, task_dealloc_GB, host, login_name, status,
 last_request_start_time, last_request_end_time, row_count, transaction_isolation_level,
 query_text, query_plan)
	(select
        t1.session_id
        , t1.request_id
        , task_alloc_GB = cast((t1.task_alloc_pages * 8./1024./1024.) as numeric(10,1))
        , task_dealloc_GB = cast((t1.task_dealloc_pages * 8./1024./1024.) as numeric(10,1))
        , host= case when t1.session_id <= 50 then 'SYS' else s1.host_name end
        , s1.login_name
        , s1.status
        , s1.last_request_start_time
        , s1.last_request_end_time
        , s1.row_count
        , s1.transaction_isolation_level
        , query_text=
            coalesce((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
              (CASE WHEN statement_end_offset = -1
                  THEN LEN(CONVERT(nvarchar(max),text)) * 2
                       ELSE statement_end_offset
                  END - t2.statement_start_offset)/2)
            FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
        , query_plan=(SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle))
    from
        (Select session_id, request_id
        , task_alloc_pages=sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count)
        , task_dealloc_pages = sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count)
        from sys.dm_db_task_space_usage
        group by session_id, request_id) as t1
    left join sys.dm_exec_requests as t2 on
        t1.session_id = t2.session_id
        and t1.request_id = t2.request_id
    left join sys.dm_exec_sessions as s1 on
        t1.session_id=s1.session_id
    where
        t1.session_id > 50 -- ignore system unless you suspect there's a problem there
        and t1.session_id <> @@SPID -- ignore this request itself
    )
    GO

--from https://littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-whats-their-plan/

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:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'TempdbGrowth', 
	@enabled=1, 
	@delay_between_responses=0, 
	@include_event_description_in=1, 
	@performance_condition=N'Databases|Data File(s) Size (KB)|tempdb|>|98304000', 
	@job_id=N'b02b2790-cde8-4c18-89fa-380b20d9a953'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'TempdbGrowth', @operator_name=N'YourOperatorName', @notification_method = 1
GO

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!

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

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

  • >