USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--job name you want to monitor
--percentage difference threshold
--db mail settings
CREATE PROCEDURE [dbo].[AlertOnLongRunningJobs] (
@jobName NVARCHAR(100)
,@pct DECIMAL(10, 2)
,@db_mail_profile NVARCHAR(100)
,@db_mail_recipients NVARCHAR(100)
)
AS
SET NOCOUNT ON
--get email address for operator
SELECT @db_mail_recipients = email_address from msdb.dbo.sysoperators where name = @db_mail_recipients
--the percentage increase between the average run time and the runtime of the job running now
DECLARE @increase DECIMAL(10, 2)
DECLARE @runtime DECIMAL(10, 2)
DECLARE @avgRunTime DECIMAL(10, 2)
--db mail settings
DECLARE @db_mail_body NVARCHAR(512)
DECLARE @db_mail_subject NVARCHAR(256)
CREATE TABLE #runningJobs (
running_job_id UNIQUEIDENTIFIER
,running_job_name NVARCHAR(100)
,runtime VARCHAR(8)
)
CREATE TABLE #jobHistory (
job_id UNIQUEIDENTIFIER
,job_name NVARCHAR(100)
,avgRunTime VARCHAR(8)
)
--check if job is running now
INSERT INTO #runningJobs
SELECT j.job_id
,j.NAME AS job_name
,CONVERT(VARCHAR, (GETDATE() - ja.start_execution_date), 108)
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps js ON ja.job_id = js.job_id
AND ISNULL(ja.last_executed_step_id, 0) + 1 = js.step_id
WHERE ja.session_id = (
SELECT TOP 1 session_id
FROM msdb.dbo.syssessions
ORDER BY agent_start_date DESC
)
AND start_execution_date IS NOT NULL
AND stop_execution_date IS NULL
AND j.NAME = @jobName
--if the job is running now, then we execute rest of query, or we just stop here.
IF EXISTS (
SELECT 1
FROM #runningJobs
)
INSERT INTO #jobHistory
SELECT jobid = job_id
,job_name = NAME
,avg_hhmmss = CONVERT(CHAR(8), DATEADD(second, rd, 0), 108)
FROM (
SELECT j.NAME
,j.job_id
,COUNT(*) AS NumberOfRuns
,rd = AVG(DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 0, ':'), 3, 0, ':')))
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
WHERE h.step_id = 0
AND j.NAME = @jobName
GROUP BY j.NAME
,j.job_id
) AS rs
ORDER BY job_name
--get run time of current job and average runtime
SELECT @runtime = DATEDIFF(second, 0, runtime)
FROM #runningJobs
SELECT @avgRunTime = DATEDIFF(SECOND, 0, avgRunTime)
FROM #jobHistory
--set the percentage increase of the current run of job.
--if it is greater than percentage threshold then return job name.
SET @increase = @runtime - @avgRunTime
SET @increase = (@increase / @avgRunTime) * 100
IF (@increase > @pct)
BEGIN
SET @db_mail_subject = 'Long Running SQL Agent Job on ' + (
SELECT @@SERVERNAME
)
SET @db_mail_body = 'The job ' + @jobName + ' has been running for ' + (
SELECT runtime
FROM #runningJobs
) + ' (hhmmss). The avergage duration for this job is ' + (
SELECT avgRunTime
FROM #jobHistory
) + '(hhmmss).
This is a ' + CAST(@increase AS NVARCHAR(12)) + '% increase. Please investigate.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @db_mail_profile
,@recipients = @db_mail_recipients
,@body = @db_mail_body
,@subject = @db_mail_subject;
DROP TABLE #runningJobs
DROP TABLE #jobHistory
END
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AlertOnFailedLogShippedJobs] (
@DaysPast INT
,@db_mail_profile NVARCHAR(100)
,@db_mail_recipients NVARCHAR(100)
)
AS
SET NOCOUNT ON;
DECLARE @Value [varchar] (2048)
,@JobName [varchar] (2048)
,@PreviousDate [datetime]
,@Year [varchar] (4)
,@Month [varchar] (2)
,@MonthPre [varchar] (2)
,@Day [varchar] (2)
,@DayPre [varchar] (2)
,@FinalDate [int]
-- Declaring Table variable
DECLARE @FailedJobs TABLE (
[JobName] [varchar](200)
,[RunDate] NVARCHAR(20)
,[RunTime] VARCHAR(16)
)
--db mail settings
DECLARE @db_mail_body NVARCHAR(MAX)
DECLARE @db_mail_subject NVARCHAR(128)
-- Initialize Variables
SET @PreviousDate = DATEADD(DAY, - @DaysPast, GETDATE())
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT([varchar](2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT([varchar], (@MonthPre + 1000000000)), 2)
SELECT @DayPre = CONVERT([varchar](2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT([varchar], (@DayPre + 1000000000)), 2)
SET @FinalDate = CAST(@Year + @Month + @Day AS [int])
SELECT @db_mail_recipients = email_address
FROM msdb.dbo.sysoperators
WHERE NAME = @db_mail_recipients
-- Final Logic
INSERT INTO @FailedJobs
SELECT DISTINCT j.[name] AS JobName
,STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), h.run_date), 8), 7, 0, '-'), 5, 0, '-') AS RunDate
,STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(6), h.run_time), 6), 5, 0, ':'), 3, 0, ':') AS RunTime
FROM [msdb].[dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo].[sysjobs] j ON h.[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo].[sysjobsteps] s ON j.[job_id] = s.[job_id]
AND h.[step_id] = s.[step_id]
WHERE h.[run_status] = 0
AND h.[run_date] > @FinalDate
AND j.[name] LIKE 'LS%'
--remove the above line for all jobs
IF @@ROWCOUNT > 0
BEGIN
DECLARE @lastCopiedFile NVARCHAR(100)
,@LastCopiedDate DATETIME
,@LastRestoreFile NVARCHAR(100)
,@LastRestoredDate DATETIME
SELECT @lastCopiedFile = last_copied_file
,@LastCopiedDate = last_copied_date
,@LastRestoreFile = last_restored_file
,@LastRestoredDate = last_restored_date
FROM msdb.dbo.log_shipping_monitor_secondary
SET @db_mail_subject = 'Failed Log Shipping Job on ' + (
SELECT @@SERVERNAME
)
SET @db_mail_body = N'
<H1>Failed Log Shipping Job</H1>
' + N'
<h3>The following Log Shipping jobs have failed in the past 24 hours</h3>
' + '
The last copied file was ' + @lastCopiedFile + ' at ' + CAST(@LastCopiedDate AS NVARCHAR(20)) + '. The last restored file was ' + @LastRestoreFile + ' at ' + CAST(@LastRestoredDate AS NVARCHAR(20)) + '.' + CHAR(10) + CHAR(13) + N'
<table border="1">' + N'
<tr>
<th>JobName</th>
' + N'
<th>RunDate</th>
' + N'
<th>RunTime</th>
' + '</tr>
' + cast((
SELECT td = FJ.JobName
,''
,td = FJ.RunDate
,''
,td = FJ.RunTime
,''
FROM @FailedJobs FJ
FOR XML path('tr')
,type
) AS NVARCHAR(max)) + N'</table>
' + CHAR(10) + CHAR(13) + '
Please investigate.'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @db_mail_profile
,@recipients = @db_mail_recipients
,@body = @db_mail_body
,@body_format = 'HTML'
,@subject = @db_mail_subject
END
GO
DECLARE @path NVARCHAR(50) = 'M:\LS_Backups'
DECLARE @LastRestoreFileDate DATETIME
SELECT @LastRestoreFileDate = CAST(STUFF(STUFF(STUFF(STUFF(STUFF(RIGHT(LEFT(last_restored_file, LEN(last_restored_file) - 4), LEN(last_restored_file) - 45), 11, 0, ':'), 14, 0, ':'), 5, 0, '-'), 8, 0, '-'), 11, 0, ' ') AS DATETIME)
FROM msdb.dbo.log_shipping_secondary_databases
DECLARE @DeleteDate DATETIME = DATEADD(HOUR, - 8, @LastRestoreFileDate)
EXEC master.sys.xp_delete_file 0
,@path
,'trn'
,@DeleteDate
,0;
USE [msdb]
GO
/****** Object:StoredProcedure [dbo].[MonitorCustomLogShippingRestore]Script Date: 11/09/2015 12:34:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MonitorCustomLogShippingRestore]
@restoreJobName SYSNAME
,@Database SYSNAME
,@db_mail_profile NVARCHAR(100)
,@db_mail_recipients NVARCHAR(100)
AS
--db mail settings
DECLARE @db_mail_body NVARCHAR(MAX)
DECLARE @db_mail_subject NVARCHAR(128)
DECLARE @DBState NVARCHAR (20)
DECLARE @DBUserAccessState NVARCHAR (20)
DECLARE @DBIsInStandby NVARCHAR (32)
SELECT @db_mail_recipients = email_address from msdb.dbo.sysoperators where name = @db_mail_recipients
SELECT 1
FROM [master].[sys].[databases]
WHERE name = @Database
AND is_read_only = 1
IF @@ROWCOUNT = 0
BEGIN
SET @db_mail_subject = 'Database '+@Database+' Not In Correct State on Server' + (
SELECT @@SERVERNAME
)
SET @db_mail_body =N'<h3>The databse '+@Database+' is in the incorrect state. The job '+@restoreJobName+' has completed restoring all backup files.
The database '+@database+' is '+@DBState+'. The restriction accces is '+@DBUserAccessState+'. The database '+@database+' is '+@DBIsInStandby+'.
To resolve, run another restore using the STANDBY option, and investigate the root cause of the issue.</h3>'
EXEC msdb.dbo.sp_send_dbmail @profile_name = @db_mail_profile
,@recipients = @db_mail_recipients
,@body = @db_mail_body
,@body_format = 'HTML'
,@subject = @db_mail_subject
END
GO
