Maintenance Plan Backup Fails (Sometimes)

Question

Post reply

Maintenance Plan Backup Fails (Sometimes)

Michael Obrycki

Valued Member

Points: 72
More actions
March 3, 2005 at 7:56 am

#113465

Hello, thank you in advance for any help you can give.
Here is what I am working with:
SQL Server 2000 Standard
8.00.760 (SP3)
3GB RAM
Dual Xeon 2.4GHz
I have a maintenance plan that flows like this:
Operates on one database
7:45PM run optimizations
7:50PM run integrity checks
8:00PM run full backup, verify integrity
Between 6:00AM-6:00PM backup transaction logs every 10 minutes
Report on each step
Each step works every day, every time - with the exception of the
backup. This works about 25% of the time. If I watch it fail and
attempt to start the job again, it may fail, it may work - again 25% of
the time. If I cannot get that job to run, I go to the "Backup"
section in the Management tab of Enterprise Manager and run a backup on
the database there. That works every time.
My question is why will the backup not run as a job but it will run
when i run it manually through "Backup"?
Here is the TSQL the job has for the backup:
---- BEGIN TSQL FOR Reference -----
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
61A2BCA5-2FBE-4AD1-93C6-3EB262BC1E2B -Rpt
"D:\__SQL_SERVER_BACKUPS\Reports\pubs\pubs Maintenance Plan4.txt"
-DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB
-BkUpMedia DISK -BkUpDB "D:\__SQL_SERVER_BACKUPS\Databases" -DelBkUps
3DAYS -CrBkSubDir -BkExt "BAK"'
---- END TSQL FOR Reference -----
Thank you, once more...
Michael

Viewing 7 posts - 1 through 6 (of 6 total)

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

Rich James Default port Points: 1484 More actions · Answer 1

Anything written in your server logs that says why the backup failed?

Michael Obrycki Valued Member Points: 72 More actions · Answer 2

Sorry, I meant to include those....

This is the first entry:

BACKUP failed to complete the command BACKUP DATABASE [pubs] TO DISK = 'D:\__SQL_SERVER_BACKUPS\Databases\pubs\pubs_db_200503022000.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

Then the second entry for the verify - which cannot run because backup failed:

BackupDiskFile:penMedia: Backup device 'D:\__SQL_SERVER_BACKUPS\Databases\pubs\pubs_db_200503022000.BAK' failed to open. Operating system error = 32(The process cannot access the file because it is being used by another process.).

athurgar SSCrazy Points: 2816 More actions · Answer 3

Is the backup file being backed up by a network backup process, or is the disk full?

I avoid using the DB maintenance wizard and use scripts or procs to backup databases. Why - easier to debug, you are in control and it is quicker. I tested this with 8 databases totalling 50Gb which took just over 3 hours via the maint wizard but took 100 mins in native SQL.

dbamark SSCommitted Points: 1694 More actions · Answer 4

I have found similar results, as athurgar has, using scripts instead of the maint wizzard. I would strongly recommend you try the same.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

check out my post in this thread.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=165575

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

big boy Old Hand Points: 340 More actions · Answer 6

I truly hate them. They never seem to work right for me. Some DBAs think they are great though...