Friday, April 24, 2009

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

No comments:

SQL Server 2016 Stretch Databases

El SQL Server Stretch Database permite que los datos históricos o de bitácoras puedan ser movidos hacia Azure, específicamente permite mover...