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

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server