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
--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
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
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!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.