Problem deleting old backup
-
Hello
I am using Maintenance plan to backup my 4 databases. In this plan I have option to remove files older then 1 day.
3 of 4 backups deleting jus fine, but I have a problem with biggest one.
Backup file is about 5.7 GB. And, ONCE IN A WHILE, it gives me an error in a log:
Unable to delete file <name of backup file.bak>
What's confusing me, why that's not happening every day. Let's 3-4 days of week it just fine and other days unable to delete. Days are not consistent, other words it's not like every Monday or Friday same thing happening.
No process running to lock backup file.
Any ideas or suggestions?
Thank you in advance
-
And what is the record about previous backup of this DB?
Was it successful?
_____________
Code for TallyGenerator -
I've had this problem consistenly with 3rd party tape backup software that appears to be finished backing up a file but in reality it somehow still maintains a lock on it. It's sporadic and eventually let's go of the file. I've had success stopping and restarting the service for the backup software. This will free the lock and the file can be deleted.
-
Hello and thank you for your reply's
the thing is that backup is ok, and this would not be a problem, cuz next day or day after this old backup will be deleted by next job run.
It just I kepp getting those failure messages. Backup failed(because cannot delete this old file).
Checked today. All good. Old files was deleted, so I have one backup for each DB. Tomorrow ( I am sure) it's gonna come back again with error. I can't find good explanation for this problem.
to Sergiy: yes, the record shows success
to Jane: I agree about lock, but it's hard to believe that SQL Server would kepp locking this file once in a while for 24 hours and that lock would not be visible.
-
It is not SQL that is taking the lock, it is the tape backup process.
If the success of tape backups is not properly checked, a failed backup may not get detected until the following day when the next backup is due to run. The backup operator notices the tape is stuck in the drive and does what is needed to clear it. In the meantime, files get left locked on the server.
The most worrying thing about this is it could mean your tape backup process is really flaky. If your organisation cannot rely on getting a working disaster recovery backup offsite every day, it may find the recovery bit hard to do in a disaster...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
well..what if there is no tape backup?
I am talking about backup on the drive and there is no tape drive in this server
Thank you for help
-
Do you have any backup process going on (i.e. Arcserve Backup running on a server dedicated to backing up the other servers)? That can lock the file.
We had one 2 nights ago. We had the MSSQLServer shutdown to do some other work. We started up the sql about 11:30P. The backup server was backing up the SQL server at the time. The server came up with on of the DBs suspect because it being backed up at the time. Therefore SQL couldn't get exclusive lock on the file. Normally this isn't a problem because the mdf is open and gets bypassed by the backup process.
So if the backup process is backing up the BAK file at the same time the delete command is running the SQL server says the file is open and skips the process.
The solution is to adjust the time on either the backup process or the maint plan.
Side Note: I usually keep 3 days of backup and 2 days of tran logs. I have had to go back to tape only twice, and both of those were for month-end backups several weeks out. Except for disaster testing.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
I have same thing, 3 days ob backup and 2 of tran.
So, I had a couple cases when Maint. plan tryed to delete backup files that 3 and 2 days old and failed to do so. Then next day, same files was deleted with no problems.
No other processes running on this server at the same time. I adjusted time that I have 2 hours before and after backup. backup process usually takes 25-20 min
Thank you
-
Is your SQL backup going to the local machine or to a network server?
We do our SQL Backups to a network server. Every once in a while I get this same thing occuring--it errors out and it won't delete the file. The server doesn't have the fastest disk subsystem and it typically happens when the network traffic is very high in this segment of the network. Basically the network times out and as such returns an errocode to SQL and it fails the job.
Our solution for the time being is to keep an eye on it. Longer term, we are ordering a machine with a much faster and larger disk subsystem.
SJ
-
I also forgot to ask if you are using disk compression at all? If so, that was causing us problems too!
SJ
-
Thank you for reply
We backing up to local machine, no network backup. We don;t use any disk compression at all
here is good example from today:
last morning(07/08/04) I had 2 backup files:
07062004_backup.bak
07072004_backup.bak
and error message unable to delete 07062004_backup.back
this morning(07/09/04) I have 2 files as well:
07062004_backup.bak
07082004_backup.bak
and error message unable to delete 07062004_backup.back
so, backup file 07072004_backup.bak was deleted, but 07062004_backup.bak semms to be stocked
Accepting all invitesViewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply