Create Permanent Log Table
/****** Object: Table [dbo].[LOG_MSDB_SQL_JOBS_RETRYS] Script Date: 6/29/2016 7:04:43 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_MSDB_SQL_JOBS_RETRYS](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[JobName] [varchar](100) NULL,
[RunTime] [datetime] NULL,
[StepDesc] [varchar](250) NULL,
[ErrorMsg] [varchar](max) NULL,
[InsertedDate] [datetime] NULL CONSTRAINT [DF_LOG_MSDB_SQL_JOBS_RETRYS_InsertedDate] DEFAULT (getdate()),
CONSTRAINT [PK_LOG_MSDB_SQL_JOBS_RETRYS] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
T-SQL:
Declare @DaysBack int
Set @DaysBack = -1
INSERT into dbo.LOG_MSDB_SQL_JOBS_RETRYS ([JobName],[RunTime],[StepDesc],[ErrorMsg])
SELECT
SJ.Name as 'Job Name',
msdb.dbo.agent_datetime(sjh.run_date,sjh.run_time) as 'Run Time',
'Retried Step: [' + step_name + ']' as 'Step',
message as 'Error'
FROM msdb.dbo.sysjobhistory SJH
INNER JOIN msdb.dbo.sysjobs SJ ON SJH.job_id=sj.job_id
INNER JOIN msdb.dbo.syscategories SJC ON SJ.category_id = SJC.category_id
WHERE
msdb.dbo.agent_datetime(sjh.run_date,sjh.run_time) >= DATEADD(d,@DaysBack,GetDate())
and SJH.run_status = 2 --A retry
and SJC.name not like 'REPL%' --Filter out Replication Jobs
ORDER BY SJ.name, SJH.run_date, SJH.run_time
Note:
SQL Server Agent stores SQL jobs history in sysjobhistory. It has two columns for date and time - Run_Date and Run_Time - stored as INT's.
dbo.agent_datetime is a system function in MSDB database that takes those two columns as input & outputs to a DATETIME data type.
This can then be used for SELECT column and WHERE filtering.
Without that function you will need to convert like below:
Select
...
CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */ +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */ + (run_time - (run_time/100) * 100) /* secs */ ,CONVERT(DATETIME,RTRIM(run_date),113)),100)
...
WHERE
DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113))
>= DATEADD(d,-1,GetDate())
SQL Agent Job - Retry Setting:
T-SQL Output: Retries in the last 1 Day
|
|
|
|
|
|