Case: one of user running query long time. DBA, will notify at that the session TEMP use has been more than 1 TB and kill that manfully. Its cleared temp DB space. But it is not reclaimed the temp DB drive space. to re- claim temp DB space we need to raise change request and approval from CAB meeting.
In this case we need to set a job that will need kill automatically user session when its reached to threshold values.
This SQL Agent job continuously monitors user sessions consuming TempDB space. When a session's TempDB usage reaches or exceeds the configured threshold value, the job automatically:
- Identifies the session exceeding the threshold.
- Sends an email notification to the associated user and DBA team.
- Waits for the configured grace period.
- Rechecks the session's TempDB usage.
- Automatically terminates the session if the TempDB usage remains above the threshold.
- Sends a confirmation or failure notification to the DBA team.
USE master
GO
DECLARE @ThresholdGB DECIMAL(18,2) = 100.00;
DECLARE
@SessionID INT
, @EmailAddress VARCHAR(500)
, @Body NVARCHAR(MAX)
, @KillCmd NVARCHAR(100)
, @CurrentGB DECIMAL(18,2)
, @Recipients VARCHAR(500);
IF OBJECT_ID('tempdb..#TempDBSessions') IS NOT NULL
DROP TABLE #TempDBSessions;
SELECT
s.session_id
, s.login_name
, s.host_name
, s.program_name
, DB_NAME(r.database_id) AS database_name
, CAST(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024
AS DECIMAL(18,2)
) AS TempDB_Used_GB
, r.start_time
, ee.email_address
INTO #TempDBSessions
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s
ON su.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
LEFT JOIN reference.dbo.emplye_master em
ON em.usrnam =
CASE
WHEN CHARINDEX('\',s.login_name) > 0
THEN RIGHT(s.login_name,
LEN(s.login_name) - CHARINDEX('\',s.login_name))
ELSE s.login_name
END
OUTER APPLY
(
SELECT TOP (1) email_address
FROM reference.dbo.emplye_email ee
WHERE ee.emplye_id = em.emplye_id
ORDER BY ee.mdfctn_dt DESC
) ee
WHERE s.is_user_process = 1
AND s.session_id <> @@SPID
AND s.program_name = 'Microsoft SQL Server Management Studio - Query'
AND CAST(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024
AS DECIMAL(18,2)
) >= @ThresholdGB;
DECLARE AlertCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT session_id, email_address
FROM #TempDBSessions;
OPEN AlertCursor;
FETCH NEXT FROM AlertCursor
INTO @SessionID, @EmailAddress;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Recipients = COALESCE(NULLIF(@EmailAddress,''),'kovvuru.subbareddy@cleanharbors.com');
----------------------------------------------------------------
-- Build HTML Email
----------------------------------------------------------------
SELECT @Body =
'<html><body>
<h3>Critical TempDB Usage Alert</h3>
<table border=1 cellpadding=5 cellspacing=0>
<tr><td><b>Session ID</b></td><td>' + CAST(session_id AS VARCHAR(20)) + '</td></tr>
<tr><td><b>Login Name</b></td><td>' + ISNULL(login_name,'') + '</td></tr>
<tr><td><b>Host Name</b></td><td>' + ISNULL(host_name,'') + '</td></tr>
<tr><td><b>Program Name</b></td><td>' + ISNULL(program_name,'') + '</td></tr>
<tr><td><b>Database</b></td><td>' + ISNULL(database_name,'') + '</td></tr>
<tr><td><b>TempDB Usage</b></td><td>' +
CAST(TempDB_Used_GB AS VARCHAR(20)) + ' GB</td></tr>
</table>
<br><br>
This session exceeded the configured threshold of '
+ CAST(@ThresholdGB AS VARCHAR(20)) + ' GB.
<br>Please terminate and optimize the query immediately. Else the '+'Session ID ' + CAST(@SessionID AS VARCHAR(20)) + ' will be automatically terminated after 5 Minutes.
</body></html>'
FROM #TempDBSessions
WHERE session_id = @SessionID;
----------------------------------------------------------------
-- Send Warning Email
----------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'DBA_Mail_Profile',
@recipients = @Recipients,
@copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',
@subject = 'CRITICAL ALERT - TempDB Usage Exceeded',
@body = @Body,
@body_format = 'HTML';
--------------------------------------------------------------
-- Grace Period
----------------------------------------------------------------
WAITFOR DELAY '00:05:00';
----------------------------------------------------------------
-- Recheck Current TempDB Usage
----------------------------------------------------------------
SELECT
@CurrentGB =
CAST(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count)
* 8.0 / 1024 / 1024
AS DECIMAL(18,2)
)
FROM sys.dm_db_session_space_usage su
WHERE su.session_id = @SessionID;
----------------------------------------------------------------
-- Kill only if still above threshold
----------------------------------------------------------------
IF ISNULL(@CurrentGB,0) >= @ThresholdGB
BEGIN
BEGIN TRY
DECLARE @body1 NVARCHAR(MAX) = 'Session ID ' + CAST(@SessionID AS VARCHAR(20)) + ' was automatically terminated because TempDB usage remained at '+ CAST(@CurrentGB AS VARCHAR(20))+ ' GB after the 5-minute grace period.'
DECLARE @body2 NVARCHAR(MAX) = 'Failed to terminate Session ID '+ CAST(@SessionID AS VARCHAR(20))+ CHAR(13)+CHAR(10) + ERROR_MESSAGE()
SET @KillCmd = 'KILL ' + CAST(@SessionID AS VARCHAR(20));
EXEC (@KillCmd);
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'DBA_Mail_Profile',
@recipients = @Recipients,
@copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',
@subject = 'Session Killed - TempDB Threshold Exceeded',
@body =@body1;
END TRY
BEGIN CATCH
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'DBA_Mail_Profile',
@recipients = 'kovvuru.subbareddy@cleanharbors.com',
@subject = 'Failed to Kill Session',
@body =@body2;
END CATCH
END
ELSE
BEGIN
----------------------------------------------------------------
-- Optional: Notify DBA that usage dropped below threshold
----------------------------------------------------------------
DECLARE @body3 NVARCHAR(MAX) = 'Session ID ' + CAST(@SessionID AS VARCHAR(20)) + ' reduced TempDB usage to ' + CAST(ISNULL(@CurrentGB,0) AS VARCHAR(20)) + ' GB. Session was not terminated.';
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'DBA_Mail_Profile',
@recipients = 'kovvuru.subbareddy@cleanharbors.com',
@subject = 'TempDB Usage Reduced - No Kill Required',
@body =@body3 ;
END
FETCH NEXT FROM AlertCursor
INTO @SessionID, @EmailAddress;
END
CLOSE AlertCursor;
DEALLOCATE AlertCursor;
GO
Below is the verification quesy:
SELECT
s.session_id,
s.login_name,
s.host_name,
s.program_name,
DB_NAME(r.database_id) AS database_name,
CAST(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024
AS DECIMAL(18,2)
) AS TempDB_Used_GB,
r.status,
r.command,
r.start_time,
em.emplye_id,
UserName =
CASE
WHEN CHARINDEX('\', s.login_name) > 0
THEN SUBSTRING(
s.login_name,
CHARINDEX('\', s.login_name) + 1,
LEN(s.login_name)
)
ELSE s.login_name
END,
ee.email_address
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions s
ON su.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
LEFT JOIN reference.dbo.emplye_master em
ON em.usrnam =
CASE
WHEN CHARINDEX('\', s.login_name) > 0
THEN SUBSTRING(
s.login_name,
CHARINDEX('\', s.login_name) + 1,
LEN(s.login_name)
)
ELSE s.login_name
END
OUTER APPLY
(
SELECT TOP (1)
email_address
FROM reference.dbo.emplye_email e
WHERE e.emplye_id = em.emplye_id
ORDER BY e.mdfctn_dt DESC
) ee
WHERE
(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024
) >= 1
AND s.is_user_process = 1
ORDER BY TempDB_Used_GB DESC;
--EXEC msdb.dbo.sp_send_dbmail
-- @recipients = 'kumar.kiran2@cleanharbors.com;qi.guang@cleanharbors.com;jampanna.sudhakar@cleanharbors.com;kovvuru.subbareddy@cleanharbors.com', --;jampanna.sudhakar@cleanharbors.com
-- @body = @body,
-- @body_format ='HTML',
-- @subject = @sub
Below one not working need to work:
DECLARE @ThresholdGB DECIMAL(18,2) = 1;
DECLARE
@SessionID INT
, @LoginName SYSNAME
, @HostName VARCHAR(256)
, @ProgramName VARCHAR(500)
, @DatabaseName SYSNAME
, @TempDBGB DECIMAL(18,2)
, @StartTime DATETIME
, @EmailAddress VARCHAR(500)
, @Body NVARCHAR(MAX)
, @KillCmd NVARCHAR(100);
DECLARE TempDB_Cursor CURSOR FAST_FORWARD
FOR
SELECT
s.session_id
, s.login_name
, s.host_name
, s.program_name
, DB_NAME(r.database_id) AS database_name
, CAST(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024
AS DECIMAL(18,2)
) AS TempDB_Used_GB
, r.start_time
, ee.email_address
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s
ON su.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
LEFT JOIN reference.dbo.emplye_master em
ON em.usrnam =
CASE
WHEN CHARINDEX('\',s.login_name) > 0
THEN RIGHT(s.login_name,
LEN(s.login_name) - CHARINDEX('\',s.login_name))
ELSE s.login_name
END
OUTER APPLY
(
SELECT TOP(1)
email_address
FROM reference.dbo.emplye_email ee
WHERE ee.emplye_id = em.emplye_id
ORDER BY ee.mdfctn_dt DESC
) ee
WHERE
s.is_user_process = 1
AND s.session_id <> @@SPID
AND CAST(
(su.user_objects_alloc_page_count +
su.internal_objects_alloc_page_count) * 8.0 / 1024 / 1024
AS DECIMAL(18,2)
) >= @ThresholdGB
AND s.login_name IN
(
'Microsoft SQL Server Management Studio - Query'
);
OPEN TempDB_Cursor;
FETCH NEXT FROM TempDB_Cursor
INTO
@SessionID
, @LoginName
, @HostName
, @ProgramName
, @DatabaseName
, @TempDBGB
, @StartTime
, @EmailAddress;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @recipients1 VARCHAR(500)= COALESCE(NULLIF(@EmailAddress,''),'kovvuru.subbareddy@cleanharbors.com')
SET @Body =
'<html><body>' +
'<h3>TempDB Usage Alert</h3>' +
'<p>The following session has exceeded the configured TempDB threshold of '
+ CAST(@ThresholdGB AS VARCHAR(20)) + ' GB.</p>' +
'<table border=1 cellpadding=5 cellspacing=0>' +
'<tr><td><b>Session ID</b></td><td>' + CAST(@SessionID AS VARCHAR(20)) + '</td></tr>' +
'<tr><td><b>Login Name</b></td><td>' + ISNULL(@LoginName,'') + '</td></tr>' +
'<tr><td><b>Host Name</b></td><td>' + ISNULL(@HostName,'') + '</td></tr>' +
'<tr><td><b>Program Name</b></td><td>' + ISNULL(@ProgramName,'') + '</td></tr>' +
'<tr><td><b>Database</b></td><td>' + ISNULL(@DatabaseName,'') + '</td></tr>' +
'<tr><td><b>TempDB Usage (GB)</b></td><td>' + CAST(@TempDBGB AS VARCHAR(50)) + '</td></tr>' +
'<tr><td><b>Start Time</b></td><td>' + CONVERT(VARCHAR(30),@StartTime,120) + '</td></tr>' +
'</table>' +
'<br><br>This session has exceeded the permitted TempDB usage threshold.' +
'<br>Please review the execution immediately.' +
'<br><br>Regards,' +
'<br>SQL DBA Team' +
'</body></html>';
--EXEC msdb.dbo.sp_send_dbmail
-- -- @recipients = COALESCE(NULLIF(@EmailAddress,''),'kovvuru.subbareddy@cleanharbors.com')
-- @recipients = 'kovvuru.subbareddy@cleanharbors.com',
-- @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',
-- @subject = 'CRITICAL ALERT - TempDB Usage Exceeded Threshold',
-- @body = @Body,
-- @body_format = 'HTML';
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'DBA_Mail_Profile',
@recipients = @recipients1,
@copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',
@subject = 'ALERT: TempDB Usage Exceeded 100 GB',
@body = @Body,
@body_format = 'HTML';
------------------------------------------------------------------
-- Wait 1 minute before killing (optional)
------------------------------------------------------------------
--WAITFOR DELAY '00:01:00';
------------------------------------------------------------------
-- Kill Session
------------------------------------------------------------------
--SET @KillCmd = 'KILL ' + CAST(@SessionID AS VARCHAR(20));
--BEGIN TRY
-- --EXEC (@KillCmd);
-- EXEC msdb.dbo.sp_send_dbmail
-- --@profile_name = 'DBA_Mail_Profile',
-- @recipients = @recipients1,
-- @copy_recipients = 'kovvuru.subbareddy@cleanharbors.com',
-- @subject = 'Session Terminated - TempDB Threshold Exceeded',
-- @body =
-- 'Session ID '
-- + CAST(@SessionID AS VARCHAR(20))
-- + ' was automatically terminated because TempDB usage reached '
-- + CAST(@TempDBGB AS VARCHAR(50))
-- + ' GB.';
--END TRY
--BEGIN CATCH
-- EXEC msdb.dbo.sp_send_dbmail
-- --@profile_name = 'DBA_Mail_Profile',
-- @recipients = @recipients1,
-- @subject = 'Failed to Kill Session',
-- @body =
-- 'Failed to terminate Session ID '
-- + CAST(@SessionID AS VARCHAR(20))
-- + CHAR(13) + CHAR(10)
-- + ERROR_MESSAGE();
--END CATCH;
FETCH NEXT FROM TempDB_Cursor
INTO
@SessionID
, @LoginName
, @HostName
, @ProgramName
, @DatabaseName
, @TempDBGB
, @StartTime
, @EmailAddress;
END
CLOSE TempDB_Cursor;
DEALLOCATE TempDB_Cursor;
GO
