DB backup using maintenance plan

[cyno]

Hi Guys - We have DB maintenance plan that will backup the user databases everynight. Its full backup. Everything works fine. After running for certain days the backup job fails. Its Sql 2000 stnd on win 2000. Here is the error message in the job history and event log

SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB1'' (0x6869B56F61EB5A728B243C7624AABA6E) - Status: Failed - Invoked on: 2005-06-04 01:00:03 - Message: The job failed. The Job was invoked by Schedule 7 (Schedule 1). The last step to run was step 1 (Step 1).

But when i looked into the sql log there is no error and i can see the backup file existing on the backup drive. Is there anything that i am missing???? It happens once in a while and not all the time

Anyone have any ideas why SQL server will behaves like this???

 

[Catadmin]

Do you have any other jobs which conflict with this one?

That is the main problem I see when it happens to me. Check especially for Optimization or ReIndexing jobs or any hand scripted jobs that kills user connections and sets the DB to Single User mode.

Also, look for conflicting DB backups (like a Master/Model/MSDB backup happening at the same time) or for any DTS jobs that might be taking up processor threads.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[cyno]

The system DB backups are also scheduled to run at the same time. Is that causing any proble??

Cyno

 

[Catadmin]

Probably. Switch the schedule of the System DBs and see if that resolves the problem. Go into Job -> Properties -> Schedule. There should be an Edit Schedule button at the bottom of the screen.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[cyno]

Yes i will make the changes and see if it resolves this issue. But how does this process conflict with user DB backup process???? Just curious.... Anyways thanks a lot for your help

Cyno

 

[Catadmin]

Backups take up a decent amout of processing power and SQL threads. I don't know the exact program mechanics of it, but I do know that if I do a DB backup manually, I can't get the DB in question to respond to anything else (can't open tables, etc) until it's done the majority of its work.

So, my theory is that when you have more than one backup happening at the same time, it kicks out the lower priority backup. Certainly I've noticed that if I use DBMP to schedule multi-DB backups, the backups are done in order, *not* simultaneously, even if they are all in the same step.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[cyno]

Thanks for the info.

Cyno