SQL Agent error history

• sizal0234

  SSCrazy

  Points: 2272

  More actions

  September 23, 2020 at 9:00 pm

  #3790489

  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 Enthusiast

  Points: 151

  More actions

  September 24, 2020 at 12:56 pm

  #3790668

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

• sizal0234

  SSCrazy

  Points: 2272

  More actions

  September 24, 2020 at 3:14 pm

  #3790713

  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: 90860

  More actions

  September 24, 2020 at 5:55 pm

  #3790760

  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: 101192

  More actions

  September 25, 2020 at 3:55 pm

  #3790985

  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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".