SQL Agent error history

  • sizal0234

    SSCrazy

    Points: 2230

    Is there a way to capture 2 things, when did the job failed and why did it fail ( capturing error message). I don't have the history available for couple of jobs but do know they have failed before and they were fixed. I want to know why they failed before like by looking at the various errors or failed instances for like a year.

     

  • Cebisa

    SSC Journeyman

    Points: 97

    So you need to go back a year? I would see what there is in msdb.dbo.sysjobhistory

  • sizal0234

    SSCrazy

    Points: 2230

    year was just an example, I would say as long as I can go back if that is possible. Not more than a  year for sure.

  • Sue_H

    SSC Guru

    Points: 90814

    If the history isn't available in sysjobhistory you'd have to do something like find old backups of msdb that would have the history and restore them on a test, dev, sandbox or whatever  server (using a different database name) . But for system databases, the server you restore to needs to be the same version, build number as the backup so it can be a bit of a challenge. And then finding the correct older msdb, if it exists...it can all be quite a bit of work.

    Sue

  • ScottPletcher

    SSC Guru

    Points: 98608

    Restore old msdb backup(s) to a different db name.  We typically add '_' and the date of the backup to the name.  Something like this:

    RESTORE DATABASE msdb_20190915 FROM DISK = 'x:\full\path\to\backup\folder\msdb.bak' WITH MOVE 'MSDBData' TO 'n:\full\path\of\where\to\put\restored\datafile\MSDBData_20190915.mdf', MOVE 'MSDBLog' TO 'l:\full\path\of\restored\logfile\MSDBLog_20190915.ldf'

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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