September 1, 2009 at 2:25 am
Hello gents
The situation is that I am using an SSIS package made through maintenance plan to backup several databases in a roll on a daily basis. Yesterday the job of "backup plan" generated with the creation of SSIS package failed. And when I open the job history in the "log file viewer", the message is pretty long showing many databases were actually backed up successfully. But at the end of the job history log, the text says "Executing query "BACKUP DATAB... The package execution fa... The step failed.,01:37:50,0,0,,,,0". So basically, the name of the database that failed to be backed up is unknown. Is there another way besides seeing into the "job history" to locate which databases failed to be backed up?
Thanks very much!
Bazinga!
September 1, 2009 at 2:31 am
Go to the job step, the advanced options. There's an option there to log to a file. Use that, it'll log the full error message.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 5:21 am
Thanks Gail. So basically, I can't get yesterday's full backup error message anyway then?
Bazinga!
September 1, 2009 at 6:25 am
Not that I'm aware of.
If you're just interested in which were backed up and which were not, query the backup history tables in MSDB. There's a few of them, backupset and related ones. Can't recall offhand the other names
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 6:38 am
information on backups is also written to the SQL errorlog, so you could check in there.
---------------------------------------------------------------------
September 1, 2009 at 1:07 pm
Instead of looking at the job history - try reviewing the maintenance plan history instead. You might find additional information there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 2, 2009 at 7:25 am
Jeffrey Williams (9/1/2009)
Instead of looking at the job history - try reviewing the maintenance plan history instead. You might find additional information there.
I just had a look at the maintenance plan history, yes, it has the info which the backup failed. Thanks very much.
But I couldn't find any backup failure info in the error log or did I miss something?
For now, I know the failure reason is due to disk space shortage and which db need to be backed up again.
Bazinga!
September 2, 2009 at 8:13 am
xnleiden (9/2/2009)
Jeffrey Williams (9/1/2009)
Instead of looking at the job history - try reviewing the maintenance plan history instead. You might find additional information there.I just had a look at the maintenance plan history, yes, it has the info which the backup failed. Thanks very much.
But I couldn't find any backup failure info in the error log or did I miss something?
For now, I know the failure reason is due to disk space shortage and which db need to be backed up again.
If the backup never started there will be nothing in the errorlog, if it started and failed with a lack of space there should be an error 112 reported in the errorlog, search for that.
---------------------------------------------------------------------
September 2, 2009 at 8:31 am
george sibbald (9/2/2009)
xnleiden (9/2/2009)
Jeffrey Williams (9/1/2009)
Instead of looking at the job history - try reviewing the maintenance plan history instead. You might find additional information there.I just had a look at the maintenance plan history, yes, it has the info which the backup failed. Thanks very much.
But I couldn't find any backup failure info in the error log or did I miss something?
For now, I know the failure reason is due to disk space shortage and which db need to be backed up again.
If the backup never started there will be nothing in the errorlog, if it started and failed with a lack of space there should be an error 112 reported in the errorlog, search for that.
Yes, you are right, I just did a double-check, this time in the "SQL Server" logs instead of "SQL Server Agent" logs, the 112 error message was indeed recorded. Thanks Jeffrey again for sharing your valuable experience.
Bazinga!
September 2, 2009 at 9:31 am
You are welcome - and it is good to hear that you are able to identify the problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply