Monitor SQL Server TempDB Size and Alerting Script

By: Aakash Patel | Comments (7) | Related: More > Database Administration

Problem

SQL Server database space monitoring has two parts, checking remaining disk space and internal free space. Most monitoring software provides free space monitoring at the disk level only. Furthermore, TempDB has multiple data files, so you need to do a little math to see if the remaining disk space can accommodate the next auto growth. In this tip we look at a SQL Server stored procedure you can use to send alerts if there are space issues with TempDB.

Solution

I have written a stored procedure to monitor SQL Server TempDB free space and send an alert based on a defined threshold. It is always a good practice to pre-size the drive and growth settings, but having an alert avoids mistakes and downtime in some cases. The complete stored procedure is listed at the end of the article. Simply, update the mail profile name and the error handling section as per your needs and you are good to go.

Explanation of TempDB Monitoring Stored Procedure

I have broken up the stored procedure logic into the below steps for explanation.

Check TempDB Auto Growth Settings

Check the auto growth settings and alert if it’s set in percentages instead of absolute values.

----alert if auto-grow is set in percentage 
IF EXISTS(SELECT 1 FROM tempdb.sys.database_files WHERE is_percent_growth = 1)
BEGIN
   EXEC msdb.dbo.sp_send_dbmail 
      @profile_name ='no-reply'
     ,@recipients = @recipients
     ,@body ='Please update tempdb auto growth settings. It should be in absolute size as per best practices.'
     ,@subject ='Alert : Tempdb auto grow settings'
     ,@body_format='HTML'

   RETURN;
END

Check TempDB Available Disk Space

Find how much space left on the disk drive and what the required size for auto growth.

Auto growth for TempDB is not as simple as other user databases due to multiple data files of TempDB, especially when you have trace flags 1117 and 1118 enabled or you are on SQL Server 2016 and later. TempDB needs enough space to auto grow all data files equally and simultaneously on SQL Server 2016 and later or when above the Trace Flags are enabled.

The below T-SQL code calculates the remaining free space on the TempDB disk drive and space required for auto growth of data files and log file.

--- find remaining space on the [tempdb] disk drive 
SELECT @drive_space_rem=CONVERT(INT,dovs.available_bytes/1048576.0)
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.FILE_ID)dovs
WHERE DB_NAME(dovs.database_id)='tempdb'
  AND mf.file_id = 1
 
--- size required for data files autogrowth
SELECT @mdf_growth_req=(max(growth)/128)*count(1)
FROM tempdb.sys.database_files
WHERE type = 0
 
-- size required for log files auto growth        
SELECT @ldf_growth_req=(max(growth)/128)*count(1)
FROM tempdb.sys.database_files
WHERE type = 1

Find Internal Free Space in TempDB

Find total internal free space across the data files and log file.

We will use Perfmon counters within SQL Server to read internal free space values for data files and log file. Free space values are in MB/percentages, so that it can be compared to defined threshold in either size (MB/percentage) later in the procedure. I have noticed this piece is missing in monitoring tools I have used so far, they either allow to alert based on absolute size or percentage but not a combination.

Setting a flat threshold in percentage or size doesn’t always work. Like 2% for a very small and large drive is not the same amount of disk space and you may not want to get woken up in the middle of the night for nothing. This procedure gives you the flexibility to set the threshold in either unit and is very useful while working on servers of different scales or environments.

--- read internal free space for data files and log files
SELECT
   @mdf_internal_free_mb = [Free Space in tempdb (KB)] /1024 ,
   @mdf_internal_free_perc = ((([Free Space in tempdb (KB)] / 1024)*100)/([Data File(s) Size (KB)]/1024)),
   @ldf_internal_free_mb = (([Log File(s) Size (KB)]/1024)-([Log File(s) Used Size (KB)]/1024)),
   @ldf_internal_free_perc = ( 100- [Percent Log Used])
FROM (SELECT counter_name, cntr_value,cntr_type 
      FROM sys.dm_os_performance_counters
      WHERE counter_name IN
        ('Data File(s) Size (KB)',
         'Free Space in tempdb (KB)',
         'Log File(s) Size (KB)',
         'Log File(s) Used Size (KB)',
         'Percent Log Used'
        )
        AND (instance_name = 'tempdb' or counter_name like '%tempdb%')
     ) AS A
PIVOT
     ( MAX(cntr_value)FOR counter_name IN
      ([Data File(s) Size (KB)],
       [Free Space in tempdb (KB)],
       [LOG File(s) Size (KB)],
       [Log File(s) Used Size (KB)],
       [Percent Log Used])
      )AS B

Determine if SQL Server Alerts Should Be Sent

Evaluate if we need to alert the DBA team. If internal free space is under the defined threshold and remaining disk space cannot accommodate the next auto growth, send an alert.

---- check the data files free space against threshold
IF(@mdf_growth_req> @drive_space_rem and (@mdf_internal_free_perc < @mdf_internal_perc_threshold OR @mdf_internal_free_mb< @mdf_internal_mb_threshold))
BEGIN
   SET @mdfsubject= @mdfsubject+'Alert : Tempdb data files short on space '+' On '

   SET @mdftableHTML = N'<h3> '+'Total tempdb remaining space '+'</h3>'+CHAR(13)
   SET @mdftableHTML = @mdftableHTML+N'<table border="2" cellpadding="2" cellspacing="0">'+CHAR(13)
   SET @mdftableHTML = @mdftableHTML+N'<tr><th>free_space_datafiles</th><th>free_space_log_file</th></tr>'+CHAR(13)
   SET @mdftableHTML = @mdftableHTML+CAST((SELECT @mdf_internal_free_mb as TD,
                                                  @ldf_internal_free_mb AS TD 
                                           FOR XML RAW ('tr'),ELEMENTS)as varchar(MAX))+CHAR(13)
   SET @mdftableHTML = @mdftableHTML+N'</table>'+'<br>'+CHAR(13)
   SET @mdfsubject = @mdfsubject+@@SERVERNAME

   -- send an email
   EXEC msdb.dbo.sp_send_dbmail  
       @profile_name='no-reply'
      ,@recipients = @recipients
      ,@body = @mdftableHTML
      ,@subject = @mdfsubject
      ,@body_format='HTML'
END
 
---- check the log file free space against threshold 
IF(@ldf_growth_req > @drive_space_rem and( @ldf_internal_free_perc < @ldf_internal_perc_threshold OR @ldf_internal_free_mb < @ldf_internal_mb_threshold))
BEGIN
   SET @ldfsubject= @ldfsubject+'Alert : Tempdb log file short on space '+' On '

   SET @ldftableHTML = N'<h3> '+'Total tempdb remaining space '+'</h3>'+CHAR(13)
   SET @ldftableHTML = @ldftableHTML+N'<table border="2" cellpadding="2" cellspacing="0">'+CHAR(13)
   SET @ldftableHTML = @ldftableHTML+N'<tr><th>free_space_datafiles</th><th>free_space_log_file</th></tr>'+CHAR(13)
   SET @ldftableHTML = @ldftableHTML+CAST((SELECT @mdf_internal_free_mb as TD,
                                                  @ldf_internal_free_mb AS TD 
                                           FOR XML RAW('tr'),ELEMENTS)as varchar(MAX))+CHAR(13)
   SET @ldftableHTML = @ldftableHTML+N'</table>'+'<br>'+CHAR(13)

   SET @ldfsubject = @ldfsubject+@@SERVERNAME

   -- send an email
   EXEC msdb.dbo.sp_send_dbmail 
       @profile_name='no-reply'
      ,@recipients = @recipients
      ,@body = @ldftableHTML
      ,@subject = @ldfsubject
      ,@body_format='HTML'
END

Complete Stored Procedure to Check TempDB Space Usage and Free Space

Finally put the stored procedure in a SQL Server Agent Job and set the threshold values as per your needs and schedule the job.

You will need to make some adjustments to the stored procedure to use the Database Mail profile you have set up on your database instance.

Then just run the procedure and pass in the parameters for recipients and any of the other values if you want to use values other than the defaults I provided.

/****** Object:  StoredProcedure [dbo].[tempdb_space_alerting]   ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROC [dbo].[tempdb_space_alerting] 
--------------------------------------------------------------------------------
-- LOCATION: database name 
-- AUTHOR: Aakash Patel
-- DATE: 01/23/2019
-- INPUTS: 
 @recipients VARCHAR(200)
,@mdf_internal_mb_threshold smallint  = 2048
,@mdf_internal_perc_threshold tinyint = 2
,@ldf_internal_mb_threshold smallint  = 1024
,@ldf_internal_perc_threshold tinyint = 3
-- OUTPUTS:
-- DESCRIPTION: Monitors free space for tempdb and alerts if required. 
-- WHICH APP CALLS THIS : Agent Job
-- FREQUENCY: every xx seconds
-- MODIFICATION HISTORY: 
-- 01/23/2019 – Aakash Patel : Initial Build 
----------------------------------------------------------------------------- 
AS
BEGIN
 
BEGIN TRY
DECLARE @mdf_growth_req int
DECLARE @ldf_growth_req int
DECLARE @drive_space_rem int
DECLARE @mdf_internal_free_perc tinyint
DECLARE @mdf_internal_free_mb int
DECLARE @ldf_internal_free_perc tinyint
DECLARE @ldf_internal_free_mb int
DECLARE @mdfsubject varchar(100)=''
DECLARE @ldfsubject varchar(100)=''
DECLARE @mdftableHTML VARCHAR(MAX)
DECLARE @ldftableHTML VARCHAR(MAX)
 
----alert if auto-grow is set in percentage 
IF EXISTS(SELECT 1 FROM tempdb.sys.database_files
Where is_percent_growth=1 )
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name ='no-reply'
,@recipients = @recipients
,@body ='Please update tempdb auto growth settings. It should be in absolute size as per best practices.'
,@subject ='Alert : Tempdb auto grow settings'
,@body_format='HTML'
RETURN;
END
 
--- find remaining space on the [tempdb] disk drive 
SELECT
@drive_space_rem=CONVERT(INT,dovs.available_bytes/1048576.0)
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.FILE_ID)dovs
where DB_NAME(dovs.database_id)='tempdb'
AND mf.file_id= 1
 
--- size required for data files auto growth
select @mdf_growth_req=(max(growth)/128)*count(1)
from tempdb.sys.database_files
where type= 0

-- size required for log file auto growth        
select @ldf_growth_req=(max(growth)/128)*count(1)
from tempdb.sys.database_files
where type= 1
 
--- fetch internal free space for data files and log files
SELECT
   @mdf_internal_free_mb= [Free Space in tempdb (KB)] /1024 ,
   @mdf_internal_free_perc=((([Free Space in tempdb (KB)] / 1024)*100)/([Data File(s) Size (KB)]/1024)),
   @ldf_internal_free_mb=(([Log File(s) Size (KB)]/1024)-([Log File(s) Used Size (KB)]/1024)),
   @ldf_internal_free_perc=( 100- [Percent Log Used])
FROM (SELECT counter_name,cntr_value,cntr_type FROM sys.dm_os_performance_counters
WHERE counter_name IN
('Data File(s) Size (KB)',
'Free Space in tempdb (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)',
'Percent Log Used')
AND(instance_name='tempdb'or counter_name like'%tempdb%'))AS A
PIVOT
(MAX(cntr_value)FOR counter_name IN
([Data File(s) Size (KB)],
[Free Space in tempdb (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)],
[Percent Log Used]))AS B
 
---- check the data files free space against threshold
IF(@mdf_growth_req> @drive_space_rem and (@mdf_internal_free_perc < @mdf_internal_perc_threshold OR @mdf_internal_free_mb< @mdf_internal_mb_threshold))
BEGIN
   SET @mdfsubject= @mdfsubject+'Alert : Tempdb data files short on space '+' On '
   SET @mdftableHTML=N'<h3> '+'Total tempdb remaining space '+'</h3>'+CHAR(13)
   SET @mdftableHTML=  @mdftableHTML+N'<table border="2" cellpadding="2" cellspacing="0">'+CHAR(13)
   SET @mdftableHTML=  @mdftableHTML+N'<tr><th>free_space_datafiles</th><th>free_space_log_file</th></tr>'+CHAR(13)
   SET @mdftableHTML=  @mdftableHTML+CAST((SELECT @mdf_internal_free_mb as TD,
                                       @ldf_internal_free_mb AS TD 
      FOR XML RAW ('tr'),ELEMENTS)as varchar(MAX))+CHAR(13)
   SET @mdftableHTML=  @mdftableHTML+N'</table>'+'<br>'+CHAR(13)
   SET @mdfsubject= @mdfsubject+@@SERVERNAME
   -- send an email
   EXEC msdb.dbo.sp_send_dbmail @profile_name='no-reply'
   ,@recipients = @recipients
   ,@body = @mdftableHTML
   ,@subject = @mdfsubject
   ,@body_format='HTML'
END
 
---- check the log file free space against threshold
IF(@ldf_growth_req> @drive_space_rem and( @ldf_internal_free_perc< @ldf_internal_perc_threshold OR @ldf_internal_free_mb< @ldf_internal_mb_threshold))
BEGIN
   SET @ldfsubject= @ldfsubject+'Alert : Tempdb log file short on space '+' On '
   SET @ldftableHTML=N'<h3> '+'Total tempdb remaining space '+'</h3>'+CHAR(13)
   SET @ldftableHTML=  @ldftableHTML+N'<table border="2" cellpadding="2" cellspacing="0">'+CHAR(13)
   SET @ldftableHTML=  @ldftableHTML+N'<tr><th>free_space_datafiles</th><th>free_space_log_file</th></tr>'+CHAR(13)
   SET @ldftableHTML=  @ldftableHTML+CAST((SELECT @mdf_internal_free_mb as TD,
                                       @ldf_internal_free_mb AS TD 
      FOR XML RAW('tr'),ELEMENTS)as varchar(MAX))+CHAR(13)
   SET @ldftableHTML=  @ldftableHTML+N'</table>'+'<br>'+CHAR(13)
   SET @ldfsubject= @ldfsubject+@@SERVERNAME
   -- send an email
   EXEC msdb.dbo.sp_send_dbmail @profile_name='no-reply'
   ,@recipients = @recipients
   ,@body = @ldftableHTML
   ,@subject = @ldfsubject
   ,@body_format='HTML'
END
 
END TRY
 
----- error handling 
BEGIN CATCH
   --Insert your standard error handling 
END CATCH
 
END;
 
GO

Next Steps

Test this out in one of your test environments to see how this can be used before using in production.
Set up a SQL Server Agent job to send you daily updates on TempDB space usage.
Check out these other Database Administration Tips

About the author

Aakash Patel is a Senior SQL Server DBA for a software firm in Connecticut with 10+ years of experience.

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

View all my tips

Tuesday, May 11, 2021 - 9:16:34 AM - Aakash	Back To Top (88670)
Hi Vijay, It looks like you are missing some code, please copy the complete stored procedure listed at the end of article and try again. Thanks, Aakash

Monday, October 26, 2020 - 5:27:38 PM - Greg Robidoux	Back To Top (86699)
Hi Rob, this is showing how much free space exists in the data file and the log file. It is showing the values in MB and and as an overall percentage free value.

Tuesday, July 2, 2019 - 12:16:00 PM - Aakash	Back To Top (81658)
Good Catch Ronald , I will correct the script in the article soon.