Checking Job Failures in SQL

SQL Server Agent Tables
ID
Purpose
SQL Server 2000 Table
SQL Server 2005 Table
1
Primary table for job related information
MSDB.dbo.sysjobs
2
Entry for each step in a specific job
3
Schedule(s) for each job
4
Local or remote servers where the job executes
MSDB.dbo.sysjobservers
5
Historical record of the job's execution
MSDB.dbo.sysjobhistory
MSDB.dbo.sysjobhistory
6
Current job status, next run date\time, queued date, etc.
Not applicable
MSDB.dbo.sysjobactivity
7
Historical job step log information for all job steps configured to write to this table
Not applicable
MSDB.dbo.sysjobstepslogs
SQL Server 2000 and 2005 Code Snippets
SQL Server 2000 Code Snippet
-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 
-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days  
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) 
-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
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 
WHERE    h.run_status = 0 -- Failure 
         AND h.run_date > @FinalDate 
ORDER BY h.instance_id DESC 
 
SQL Server 2005 Code Snippet
-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 
-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -7, GETDATE()) -- Last 7 days  
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) 
-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
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 -- Failure 
         AND h.run_date > @FinalDate 
ORDER BY h.instance_id DESC 
 

Comments

Post a Comment

Popular posts from this blog

Who Deleted and Drop the record in table

CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] (     [Sr.No] INT IDENTITY,     [Date] DATETIME DEFAULT GETDATE (),     [City] CHAR (25) DEFAULT 'Bangalore');           select * from [Location]       INSERT INTO Location DEFAULT VALUES ; GO 100 DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO SELECT     [Transaction ID],     Operation,     Context,     AllocUnitName   FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS'       SELECT     Operation,     [Transaction ID],     [Begin Time],     [Transaction Name],     [Transaction SID] FROM     fn_dblog(NULL, NULL) WHERE     [Transaction ID] = '0000:000002ef' AND     [Operat...
Read more

Generate Password

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GeneratePassword](@Length int, @PasswordTemp varchar(100) output) AS BEGIN DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN         SET @RandomNumber = Rand()         SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))         SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) ...
Read more

Key Board Shortcuts

Image
100 KEYBOARD SHORTCUTS CTRL + C (Copy) CTRL + X (Cut) CTRL + V (Paste) CTRL + Z (Undo) DELETE (Delete) SHIFT + DELETE (Delete the selected item permanently without placing the item in the Recycle Bin) CTRL while dragging an item (Copy the selected item) CTRL + SHIFT while dragging an item (Create a shortcut to the selected item) F2 KEY (Rename the selected item) CTRL + RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL + LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL + DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL + UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL + SHIFT with any of the arrow keys (Highlight a block of text) SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document) CTRL + A (Select all) F3 KEY (Search for a file or a folder) ALT...
Read more