SQL agent failed job detailed informtion???

  • Hi Team,

    We configured different jobs in each sever(full,diff,tlog,dbcc and rebuild \reord,history cleanup jobs).

    We are getting alerts when the job is failed is as follows ::

    The job failed in the server XXXXXXXX on Nov 14 2012 2:15AM.Please refer the log file for detail information.

    We need to search each and evry job to find which job is failed ??.

    Is it possible to get a failure alert with the job name , then it is easy to resolve the issue quickly.

    I dont have much knowledge on scrpting side ...........

    Thanks,

    Lavanya

  • http://www.sqlmag.com/article/tsql3/have-sql-server-email-you-error-messages-generated-by-job-failures

    You will need to add a step to the end of every job which is got to by the on failure action of the other steps in the job but this is what we use to get job failure emails.

  • Hi Anthony,

    Thanks for the quick response ..

    I don't want the error message generated by that job failure . I want only the job name with the failure alert ........

  • You get an email which contains the below

    Job Name = Staging_Circle.MergeResourceToProcessWithResources

    --------------------------------------

    Step name = Circle.MergeResourceToProcessWithResources

    DB Name = Staging_Database

    Run Date = Nov 14 2012 11:08AM

    Error = Unable to connect to SQL Server 'SQL01\INSTANCE01'. The step failed.

    Command = EXEC Circle.MergeResourceToProcessWithResources

    You can obivously just go into the procedure and strip out the call to the part which gets the error and command, or use the script as a base point and create your own.

  • Lavanyasri (11/15/2012)


    I don't want the error message generated by that job failure . I want only the job name with the failure alert ........

    you can use dbo.sysjobhistory and dbo.sysjobs table to see which job is failing and why

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply