SQLDBA - High tempdb Usage: Queries Exceeding 100 MB in the Past 24 Hours

-

 USE [msdb]

GO


/****** Object:  Job [BTCH - High TEMPDB Usage Details]    Script Date: 2/20/2026 2:42:10 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Batch - H]]    Script Date: 2/20/2026 2:42:10 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Batch - H]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Batch - H]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


END


DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'BTCH - High TEMPDB Usage Details', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'High TEMPDB Usage Details', 

@category_name=N'[Batch - H]', 

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [report]    Script Date: 2/20/2026 2:42:11 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'report', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'use staging

go


DECLARE @xml1 NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)

DECLARE @today DATE = GETDATE()

DECLARE @recipient_list VARCHAR(MAX)

--CREATE TABLE #tmp1 (

--[id] INT IDENTITY(1,1) PRIMARY KEY,

--event_info NVARCHAR(MAX),

--program_name NVARCHAR(256),

--command NVARCHAR(128),

--start_time DATETIME,

--total_used_mb DECIMAL(10,2),

--login_name NVARCHAR(128),

--transaction_isolation_level NVARCHAR(50),

--dbname NVARCHAR(128)

--);



SET NOCOUNT ON

SET @recipient_list = ''ksubbareddy.sap@gmail.com''

;WITH temp1

AS

(

SELECT

EventInfo,ProgramName,start_time,login_name,transaction_isolation_level,[database_name],[TotalUsedMB]

FROM

(

SELECT

SUBSTRING(event_info,1,100) AS EventInfo,

Program_Name AS ProgramName,

--Command,

start_time,

--run_date,

--TotalUsedMB,

login_name,

transaction_isolation_level,

[database_name],

[TotalUsedMB],

ROW_NUMBER() OVER (PARTITION BY SUBSTRING(event_info,1,100) ORDER BY [TotalUsedMB] DESC) AS seq_num

FROM staging.dbo.tempdb_session_usage_detail a

WHERE a.run_date >= GETDATE() -1

AND command NOT LIKE ''%sp_MSforeachdb%''

AND command NOT LIKE ''%BACKUP DATABASE%''

AND command NOT LIKE ''%UPDATE STATISTICS%''

AND command NOT LIKE ''%sp_server_diagnostics%''

AND command NOT LIKE ''%xp_cmdshell%''

AND program_name NOT LIKE ''SQLAgent - TSQL JobStep%''

)a

WHERE a.seq_num = 1

)


SELECT @xml1 = 

''<html><body><H4>High tempdb Usage: Queries Exceeding 100 MB in the Past 24 Hours.</H4>

<table border = 1> 

<tr>

<th> EventInfo </th> <th> ProgramName </th> <th> StartTime </th> <th> LoginName </th> <th> DBName </th><th> MaxTempDBUsageMB </th> 

</tr>'' +

CAST((SELECT a.EventInfo AS ''td'', 

'''',

a.ProgramName AS ''td'',

'''',

REPLACE(CONVERT(VARCHAR(23), start_time, 126), ''T'', '' '') AS ''td'',

'''',

a.login_name AS ''td'',

'''',

ISNULL(CAST(a.[database_name] AS VARCHAR(10)),'''') AS ''td'',

'''',

a.[TotalUsedMB] AS ''td''


FROM temp1 a

ORDER BY a.[TotalUsedMB] DESC, a.EventInfo ASC

FOR XML PATH(''tr''), ELEMENTS) AS NVARCHAR(MAX)) +

N''</table>'';


SET @body = @xml1 + ''</body></html>''


EXEC msdb.dbo.sp_send_dbmail

@subject = ''High TEMPDB Usage Details in the Past 24 Hours'',

@body = @body,

@body_format =''HTML'',

@recipients = @recipient_list;



--DROP TABLE #tmp1

', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'report', 

@enabled=1, 

@freq_type=4, 

@freq_interval=1, 

@freq_subday_type=1, 

@freq_subday_interval=0, 

@freq_relative_interval=0, 

@freq_recurrence_factor=0, 

@active_start_date=20250908, 

@active_end_date=99991231, 

@active_start_time=500, 

@active_end_time=235959, 

@schedule_uid=NULL;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO





USE [msdb]

GO


/****** Object:  Job [BTCH - High TEMPDB Usage Details - DBA]    Script Date: 2/20/2026 2:42:42 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Batch - H]]    Script Date: 2/20/2026 2:42:42 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Batch - H]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Batch - H]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


END


DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'BTCH - High TEMPDB Usage Details - DBA', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'High TEMPDB Usage Details', 

@category_name=N'[Batch - H]', 

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [report]    Script Date: 2/20/2026 2:42:42 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'report', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'use staging

go


DECLARE @xml1 NVARCHAR(MAX)

DECLARE @body NVARCHAR(MAX)

DECLARE @today DATE = GETDATE()

DECLARE @recipient_list VARCHAR(MAX)

--CREATE TABLE #tmp1 (

--[id] INT IDENTITY(1,1) PRIMARY KEY,

--event_info NVARCHAR(MAX),

--program_name NVARCHAR(256),

--command NVARCHAR(128),

--start_time DATETIME,

--total_used_mb DECIMAL(10,2),

--login_name NVARCHAR(128),

--transaction_isolation_level NVARCHAR(50),

--dbname NVARCHAR(128)

--);



SET NOCOUNT ON

SET @recipient_list = ''ksubbareddy.sap@gmail.com''

;WITH temp1

AS

(

SELECT

SUBSTRING(event_info,1,100) AS EventInfo,

Program_Name AS ProgramName,

--Command,

start_time,

--run_date,

--TotalUsedMB,

(SELECT TOP 1 [TotalUsedMB]

FROM staging.dbo.tempdb_session_usage_detail b

WHERE SUBSTRING(b.event_info,1,100) = SUBSTRING(a.event_info,1,100)

AND B.Program_Name=A.Program_Name

--and b.command=a.command

and b.start_time=a.start_time

AND b.run_date >= GETDATE() -1

ORDER BY [TotalUsedMB] DESC) AS [TotalUsedMB],


login_name,

transaction_isolation_level,

[dop],

[parallel_worker_count]

--database_name as DBName

FROM staging.dbo.tempdb_session_usage_detail a

WHERE a.run_date >= GETDATE() -1

AND command NOT LIKE ''%sp_MSforeachdb%''

AND command NOT LIKE ''%BACKUP DATABASE%''

AND command NOT LIKE ''%UPDATE STATISTICS%''

AND command NOT LIKE ''%sp_server_diagnostics%''

AND command NOT LIKE ''%xp_cmdshell%''

AND program_name NOT LIKE ''SQLAgent - TSQL JobStep%''

GROUP BY 

SUBSTRING(event_info,1,100),

Program_Name,

--Command,

start_time,

--run_date,

--TotalUsedMB,

login_name,

transaction_isolation_level,

[dop],

[parallel_worker_count]

--database_name

)


SELECT @xml1 = 

''<html><body><H4>High tempdb Usage: Queries Exceeding 100 MB in the Past 24 Hours.</H4>

<table border = 1> 

<tr>

<th> EventInfo </th> <th> ProgramName </th> <th> StartTime </th> <th> TempDBUsageMB </th> <th> LoginName </th> <th> DOP </th> <th> ParallelWorkerCount </th> 

</tr>'' +

CAST((SELECT a.EventInfo AS ''td'', 

'''',

a.ProgramName AS ''td'',

'''',

REPLACE(CONVERT(VARCHAR(23), start_time, 126), ''T'', '' '') AS ''td'',

'''',

a.[TotalUsedMB] AS ''td'',

'''',

a.login_name AS ''td'',

'''',

ISNULL(CAST(a.[dop] AS VARCHAR(10)),'''') AS ''td'',

'''',

ISNULL(CAST(a.[parallel_worker_count] AS VARCHAR(10)),'''') AS ''td''


FROM temp1 a

ORDER BY a.[TotalUsedMB] DESC, a.EventInfo ASC

FOR XML PATH(''tr''), ELEMENTS) AS NVARCHAR(MAX)) +

N''</table>'';


SET @body = @xml1 + ''</body></html>''


EXEC msdb.dbo.sp_send_dbmail

@subject = ''High TEMPDB Usage Details in the Past 24 Hours'',

@body = @body,

@body_format =''HTML'',

@recipients = @recipient_list;



--DROP TABLE #tmp1

', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'report', 

@enabled=1, 

@freq_type=4, 

@freq_interval=1, 

@freq_subday_type=1, 

@freq_subday_interval=0, 

@freq_relative_interval=0, 

@freq_recurrence_factor=0, 

@active_start_date=20250908, 

@active_end_date=99991231, 

@active_start_time=1000, 

@active_end_time=235959, 

@schedule_uid=NULL;

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO



Comments

Post a Comment

Popular posts from this blog

DBA- MAXDOP

-
Image
  Have your SQL queries been eating up CPU like it's a buffet? Then it’s time to talk about the most underrated setting in SQL Server:  MAXDOP . Let’s break it down in simple, real-world terms — with best practices, optimized queries, and use cases that matter. What is MAXDOP? MAXDOP stands for Maximum Degree of Parallelism — it controls how many CPU cores SQL Server uses to run a single query in parallel. Sounds powerful? It is — but misconfigured, it can hurt more than help. Too low? Queries run slower, underutilizing hardware. Too high? CPU gets overwhelmed, blocking other tasks. 🔧  Where to Configure MAXDOP : Instance Level (Recommended for Most) EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE; Database Level (Overrides Instance Setting) ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2; Query Level (...
Read more

DBA - Check health and status of Always On or Availability Group using DMVs

-
 -- Health and status of WSFC cluster. These two queries work only if the WSFC has quorum SELECT * FROM sys.dm_hadr_cluster SELECT * FROM sys.dm_hadr_cluster_members -- Health of the AGs SELECT ag.name agname, ags.* FROM sys.dm_hadr_availability_group_states ags  INNER JOIN sys.availability_groups ag ON ag.group_id = ags.group_id -- Health and status of AG replics from the WsFC perspective SELECT ar.replica_server_name,harc.* FROM sys.dm_hadr_availability_replica_cluster_states harc  INNER JOIN sys.availability_replicas ar ON ar.replica_id = harc.replica_id -- Health and status of AG replicas, run this on the primary replica.  -- On secondary this will only show info for that instance SELECT * FROM sys.dm_hadr_availability_replica_states  -- Health and status of AG databases from the WSFC perspective SELECT * FROM sys.dm_hadr_database_replica_cluster_states  -- Health and status of AG databases, run this on the primary replica.  -- On secondary this wi...
Read more

DBA - Script out the Linked server objects.

-
  Script out the Linked server objects.        CREATE  PROCEDURE [dbo].[win_linked_server_objects_extract_select]     AS       /***********************************************************************************************************    --Purporse: To extract Linkded server connection details      --Created By: kovvuru subbareddy        Purpose:    TEST:        EXEC [dbo].[win_linked_server_objects_extract_select]       ***********************************************************************************************************/     BEGIN   SET NOCOUNT ON      CREATE TABLE #temp_linkedserver_detail   ( ID INTEGER identity(1,1) primary key NOT NULL,   create_linked_server_command VARCHAR(5000),   linked_server_name varchar(100) ...
Read more