Cómo listar los trabajos (jobs) fallidos en SQL Server
Muchas veces es necesario determinar cuáles han sido los jobs cuya ejecución ha fallado en SQL Server, para hacer esto se pueden utilizar los scripts creados por Drew Salem, los cuales incluyo a continuación:
USE
[DBA_Admin]
GO
/******
Part 3a: The Failed Jobs Report
Object Creation: Table Failed_Jobs
Purpose: To create the table Failed_Jobs
Date: 01/02/2009
Author: Drew Salem - www.thebuddingdba.com
For: SQLServerCentral.com
******/
CREATE TABLE [dbo].[Failed_Jobs]
(
[originating_server] [varchar](255) NULL,
[job_name] [varchar](255) NULL,
[job_description] [varchar](1023) NULL,
[last_outcome_message] [varchar](255) NULL,
[last_run_date] [varchar](63) NULL,
[job_id] [varchar](255) NULL
)
Después de creada esa tabla se debe crear el procedimiento almancenado para llenar la tabla anterior:
USE [DBA_Admin]
GO
/******
Part 3a: The Failed Jobs Report
Object Creation: The usp_GetFailedJob stored procedure
Purpose: To retrieve failed jobs data from a single server
Date: 08/02/2009
Author: Drew Salem - www.thebuddingdba.com
For: SQLServerCentral.com
******/
CREATE PROC [dbo].[usp_GetFailedJob]
@servername SYSNAME
AS
SET NOCOUNT ON
DECLARE @sql1 VARCHAR (8000)
DECLARE @version VARCHAR (50)
--First check which version of SQL Server is running.
SELECT @version = serverversion
FROM Server_SQL_Details
WHERE servername = @servername
--If it's 2000 then execute this code
IF @version = '8'
BEGIN
SELECT @sql1 = '
Select j.originating_server,
j.name, j.description, jh.last_outcome_message, substring(space(1),
33,33) +
-- Calculate and format fail datetime
-- Add Run Duration Seconds
cast(
-- Add Start Time Seconds
dateadd(ss, cast(substring(cast(last_run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes
dateadd(mi, cast(substring(cast(last_run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh, cast(substring(cast(last_run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (last_run_date as char(8)))))) as char(19)) As Last_Run_Date, j.job_id
FROM [' + @servername + '].msdb.dbo.sysjobservers
jh join [' + @servername + '].msdb.dbo.sysjobs j ON jh.job_id=j.job_id
where last_run_outcome <> 1'
--Insert into a temp table before it gets its knickers in a twist
CREATE TABLE #t2 (
originating_server VARCHAR(255),
job_name VARCHAR(255),
job_desc VARCHAR(1023),
last_outcome_message VARCHAR(255),
last_run_date VARCHAR(63),
job_id VARCHAR(255))
INSERT INTO #t2 EXEC(@sql1)
--And insert the relevant info into our Failed Jobs table
INSERT INTO Failed_Jobs
(originating_Server, job_name, job_description, last_outcome_message, last_run_date, job_id)
SELECT originating_server, job_name, job_desc, last_outcome_message, last_run_date, job_id
FROM #t2
DROP TABLE #t2
END
ELSE
--If it's 2005 then execute this code
IF @version = '9'
BEGIN
SELECT @sql1 = '
Select id.originating_server,
j.name, j.description, jh.last_outcome_message, substring(space(1),
33,33) +
-- Calculate fail datetime
-- Add Run Duration Seconds
cast(
-- Add Start Time Seconds
dateadd(ss, cast(substring(cast(last_run_time + 1000000 as char(7)),6,2) as int),
-- Add Start Time Minutes
dateadd(mi, cast(substring(cast(last_run_time + 1000000 as char(7)),4,2) as int),
-- Add Start Time Hours
dateadd(hh, cast(substring(cast(last_run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (last_run_date as char(8)))))) as char(19)) As Last_Run_Date, j.job_id
FROM [' + @servername + '].msdb.dbo.sysjobservers
jh join [' + @servername + '].msdb.dbo.sysjobs j ON jh.job_id=j.job_id
join [' + @servername + '].msdb.dbo.sysoriginatingservers_view id
ON id.originating_server_id=j.originating_server_id
where last_run_outcome <> 1'
CREATE TABLE #t3 (
originating_server VARCHAR(255),
job_name VARCHAR (255),
job_desc VARCHAR (1023),
last_outcome_message VARCHAR (255),
last_run_date VARCHAR(63),
job_id VARCHAR (255))
INSERT INTO #t3 EXEC(@sql1)
INSERT INTO Failed_Jobs
(originating_Server, job_name, job_description, last_outcome_message, last_run_date, job_id)
SELECT originating_server, job_name, job_desc, last_outcome_message, last_run_date, job_id
FROM #t3
DROP TABLE #t3
END
Saludos,
Eduardo Castro – Microsoft SQL Server
Costa Rica
Technorati Tags: SQL Server
LiveJournal Tags: SQL Server
del.icio.us Tags: SQL Server
Comments