Create this stored procedure in MSDB Database : this will fetch the queries which are running more than 5 min. Long running queries information will stored in MSDB.dbo.LongRunningQuery_History table and send notification to configured mail team.
USE MSDB
GO
CREATE PROCEDURE sp_longRunningQueryNotification
AS
IF (OBJECT_ID('MSDB.[dbo].[LongRunningQuery_History]') IS NULL)
BEGIN
CREATE TABLE DBAAdmin.[dbo].[LongRunningQuery_History](
[session_id] [smallint] NOT NULL
,[start_time] [datetime] NOT NULL
,[total_elapsed_time] [int] NOT NULL
,[status] [nvarchar](30)
,[command] [nvarchar](30) NOT NULL
,[database_id] [VARchar](100) NOT NULL
,[last_wait_type] [nvarchar](100)
,[wait_resource] [nvarchar](256)
,[cpu_time] [int] NOT NULL
,[reads] [bigint] NOT NULL
,[writes] [bigint] NOT NULL
,[logical_reads] [bigint] NOT NULL
,[cmdText] [varchar] (MAX)
,[user_name] [varchar] (100)
, [date] datetime
) ON [PRIMARY]
END
Declare @count varchar(100)
SELECT @count = count(*) FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p WHERE r.total_elapsed_time > 300000
IF (@count)>= 1
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Long Running Queries on '+ cast (@@servername as varchar(30)) +'</H1>' +
N'<table border="1">' +
N'<tr><th>Session_ID</th><th>Elapsed_Time_in_Mins</th><th>Command</th><th>Login_Name</th>' +
N'<th>Database_Name</th>' +
CAST ( ( SELECT td = CAST(r.session_id AS VARCHAR(10)), '',
td = CAST((r.total_elapsed_time)/60000 AS VARCHAR(10)), '',
td = CAST(t.[text] AS VARCHAR(1000)) ,'',
td = CAST(s.loginame AS VARCHAR(1000)) ,''
, td = CAST(DB_Name(r.database_id) AS VARCHAR(100) ),''
--, r.wait_type
--, r.last_wait_type
--, r.wait_resource
--, r.cpu_time
--, r.reads
--, r.writes
--, r.logical_reads
--, t.[text] AS [executing batch]
FROM
sys.dm_exec_requests r
CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
sys.dm_exec_query_plan(r.plan_handle) AS p
Left Join sys.sysprocesses s on s.spid = r.session_id
WHERE r.total_elapsed_time > 300000
ORDER BY
r.total_elapsed_time DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
DECLARE @vcsubject varchar(1000)
Set @vcsubject ='Long Running Queries in ' + cast (@@servername as varchar(30))+ ', Please start monitoring'
EXEC msdb.dbo.sp_send_dbmail @recipients='dbateam@gmail.com',
@profile_name = 'SQLDBA_Profile',
@subject = @vcsubject,
@body = @tableHTML ,
@body_format = 'HTML' ;
INSERT INTO dbo.LongRunningQuery_History SELECT r.session_id, r.start_time, TotalElapsedTime_ms = r.total_elapsed_time, r.[status], r.command, DatabaseName = DB_Name(r.database_id), r.last_wait_type, r.wait_resource, r.cpu_time, r.reads, r.writes, r.logical_reads, t.[text] , s.loginame, GETDATE() FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS p Left Join sys.sysprocesses s on s.spid = r.session_id WHERE r.total_elapsed_time > 300000
END
DELETE FROM dbo.LongRunningQuery_History WHERE [Date] < (GETDATE() - 30)
Below is the script for creating a scheduled SQL job to monitor Long running queries.
USE [msdb]
GO
/****** Object: Job [M_SQLDBA_LongRunningQueries] Script Date: 06/13/2014 14:10:59 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 06/13/2014 14:10:59 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'M_SQLDBA_LongRunningQueries',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [running] Script Date: 06/13/2014 14:10:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'running',
@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'Exec sp_longRunningQueryNotification',
@database_name=N'MSDB',
@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'LongRunningQueries',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20140612,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'e7b3e53a-e7b4-4d98-b040-1ba256b7af5a'
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
