July 20, 2009 at 8:06 am
I have some database backups that are failing inconsistently. They'll work some nights, fail others. The job was created in a maintenance plan. Basically its a Full backup linked to a Notify Operator if it fails. I have verify backup checked.
Viewing the Log File Viewer for my job, it shows that its failing with the following reason:
Date7/19/2009 2:00:00 AM
LogJob History (MyDBName.Full Backup)
Step ID1
ServerREDVS010\REDVSI010
Job NameMyDBName.Full Backup
Step NameFull Backup
Duration00:21:36
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: MYNETWORK\MSSQL. ...xecute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:00 AM Progress: 2009-07-19 02:00:08.40 Source: {79FC887F-AE74-41D3-8819-8ED9E0E371F9} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2009-07-19 02:03:03.69 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [MyDBName] TO DISK = N'T:\REDVS010_".: 50% complete End Progress Progress: 2009-07-19 02:21:34.44 Source: Back Up Database (Full) Executing query "declare @backupSetId as int select @backupSetId =".: 100% complete End Progress Progress: 2009-07-19 02:21:34.52 Source: Notify Operator Task Executing query "EXECUTE msdb.dbo.sp_notify_operator @name=N'DBA',@".: 100% complete End Progress DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:00:00 AM Finished: 2:21:34 AM Elapsed: 1. The step failed.
I really dont see what the problem is from this though. I see that it failed about 50% through, but it doesn't tell me why.
Can someone tell me where I can look this information up?
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 20, 2009 at 8:26 am
Can you see the backup file even after the job fails? If yes, can u verify if it's correct? If no, can u check disk space? May be its failing coz of some operator issues? Also can u check how much time it takes on days it successfully runs to identify whether backup is failing(which takes almost all time in this job) or the operator?
July 20, 2009 at 8:33 am
ps (7/20/2009)
Can you see the backup file even after the job fails? If yes, can u verify if it's correct? If no, can u check disk space? May be its failing coz of some operator issues? Also can u check how much time it takes on days it successfully runs to identify whether backup is failing(which takes almost all time in this job) or the operator?
The backup is not there as it failed. It normally takes about 15-20 minutes to backup. The job runs at the same time every night.
Its not this particular error thats the worry.. its that I dont see where the error is in the log. There has to be somewhere that stores the error in a more readable format.. it looks like the log viewer randomly strips off letters off words and is very incomplete.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 20, 2009 at 8:43 am
You need to set up logging in the maintance plan, then check the text files that are created. They will have more details about what is wrong.
July 20, 2009 at 8:45 am
I was hoping this information would be stored off in a table somewhere.. the logging is a pain because its so random when it fails..
oh well..
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 20, 2009 at 8:45 am
Cant think of anything as of now as nothing is in the error log.. Can you post the entire T-SQL code of the maintainence plan so that others can have a look at that?
July 21, 2009 at 5:37 pm
Let me take a guess!
In your case you are backing up multiple databases, may be one of which has been deleted/recovery mode modified...something of this sort.
Steps:
Check the maitenance file and see if you are backing up more than one database and check all the databases exist.
Check for individual recovery models(trn with simple recovery fails)
Check for the disk space etc...
Make the modifications accordingly and check for the output of next run
July 21, 2009 at 9:40 pm
The logging is trivial to set up in the plan, and there is a task to delete old logs after xx days.
You should have this running. It's not a big setup.
July 22, 2009 at 2:47 am
Also make sure the maintenance job is running with a job owner who has all sysadmin rights . At times the owner may get changed in job & be one of the cause for failure. You can also just try doing a manual back & see if it still fails
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **
July 22, 2009 at 2:52 am
Modify the maintenance plan and add a log file to that and check the file after the next run, this should surely help you.
July 22, 2009 at 5:15 am
Hi,
sorry for my badly english.
I've also sometimes some unclear errors within one or the other
maintenanceplan.
First I've tried was to replace the backup step with the generated
t-sql-code from this step with a lot of succes.
But not 100%.
And a backup thats not 100% successful isn't a real backup...
Some days ago I found this here:
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/62380/[/url]
Since I've integrated these Scripts there is no error anymore,
backups checked fine and logging is nearly 100% clear.
I suggest you have a look at this.
Greatings from Germany
Sven
July 22, 2009 at 7:07 am
dba_pkashyap (7/21/2009)
Let me take a guess!In your case you are backing up multiple databases, may be one of which has been deleted/recovery mode modified...something of this sort.
Nope. Single database in FULL recovery mode.
I never backup multiple databases under a single job.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 22, 2009 at 7:09 am
Steve Jones - Editor (7/21/2009)
The logging is trivial to set up in the plan, and there is a task to delete old logs after xx days.You should have this running. It's not a big setup.
yep.. lessons learned i guess. I'll turn on logging and wait for it to happen again
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 22, 2009 at 7:10 am
SQL DBA (7/22/2009)
Also make sure the maintenance job is running with a job owner who has all sysadmin rights . At times the owner may get changed in job & be one of the cause for failure. You can also just try doing a manual back & see if it still fails
yeh i thought maybe that, but the owner was still who it was meant to be.. and.. if i did a manual backup, it worked
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
July 22, 2009 at 8:16 am
Did you check the event viewer to see if there were any errors in the application event log at the time the job failed?
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply