Maintenance Plan Backup Fails (Sometimes)

Question

Post reply

Maintenance Plan Backup Fails (Sometimes)

Michael Obrycki

Valued Member

Points: 62
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

Rich James

Default port

Points: 1484

March 3, 2005 at 8:29 am

#545239

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

Michael Obrycki Valued Member Points: 62 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.