backup job can't remove old .BAK from disk

Hello,
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
JohnCould you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>|||We have a space problem, but more because of our Storage Administration
team.
The default behaviour of the Maintenance Plans is to backup up and then
verify each database in the plan. Only after all backups are completed,
without error, the file delete portion runs for yesterday's backup. So, you
need space for at least 2 Full backups and any retained transaction log
backups in between.
The way we've coded it is to go ahead and set the Maintenance Plans, but
then code another job that runs several times per day checking the Archive
Bit on the prior backup files. Whenever the tape client runs, it will reset
this bit allowing our job to remove it. This reduces our space requirements
to just 1 set of Full backups and intra-day transaction log backups.
Also, we write our backups to dedicated disk partitions that are compressed.
You might also consider something like SQL Litespeed, which further
compresses the database backup files but integrates nicely withing the
Maintenance Plans already created.
Hope this helps.
Sincerely,
Anthony Thomas
"randy" <randydoub@.yahoo.com> wrote in message
news:1beb01c4dc68$9f66cb00$a301280a@.phx.gbl...
I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>