January 3, 2006 at 6:46 am
Happy 2006 all
randomly, I have a number or backups that fail, and upon further examination, it appears to that the backup itself works as desired, it is when it tries to delete old backup files that it fails.
Job 'DB Backup Job for DB Maintenance Plan 'DB Maint Plan- Backup db'' : Step 1, 'Step 1' : Began Executing 2006-01-02 20:00:00
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
Logged on to SQL Server '<server>'
as '<user>' (non-trusted)
(null)
Starting maintenance plan 'DB Maint Plan- Backup db' on 1/2/2006 8:00:01 PM
(null)
[1] Database <DB>: Database Backup...
(null)
Destination: [Q:\DBBackups\<DB>\<DB>_db_200601022000.BAK]
(null)
(null)
** Execution Time: 0 hrs, 14 mins, 58 secs **
(null)
(null)
[2] Database <DB>: Verifying Backup...
(null)
(null)
** Execution Time: 0 hrs, 2 mins, 13 secs **
(null)
(null)
[3] Database <DB>: Delete Old Backup Files...
(null)
Unable to delete file Q:\DBBackups\<DB>\<DB>_db_200512312000.BAK.
1 file(s) deleted.
(null)
(null)
Deleting old text reports...
1 file(s) deleted.
(null)
(null)
End of maintenance plan 'DB Maint Plan- Backup db' on 1/2/2006 8:17:13 PM
(null)
SQLMAINT.EXE Process Exit Code: 1 (Failed)
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
As you may notice, one file was deleted, that was the file from the previous day that failed. Why would it not work the one day, but will delete the next?
Any help would be greatly appreciated. This is happening on numerous servers randomly. I was thinking it may be that because the backup is running at that time and had a lock on the file, or a virus scan...however, the virus scan does not appear to be running at this time...
Cory
-- Cory
January 3, 2006 at 8:09 am
I'd guess some type of backup grabbing the file. Any delay would cause SQL to fail as it's very intolerant of delays.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 3, 2006 at 12:38 pm
Typically this occurs when a filesystem level backup is occuring when SQL tries to delete the files. It's also possible that an anti-virus or spyware program is locking the files when the delete occurs.
January 4, 2006 at 7:57 am
You say you are not doing a virus scan, but most virus scanners include "on access" scanning as well as "on demand" scanning - the "on demand" scanning is the typically scheduled whole-system scan. Try configuring your virus scanner's on access scanner to ensure it does not scan any of your SQL Server DB files or your backup target directories...
July 28, 2010 at 2:44 am
Steve Jones - Editor (1/3/2006)
I'd guess some type of backup grabbing the file. Any delay would cause SQL to fail as it's very intolerant of delays.
Hi,
I have set retention period one day. when sql server tries to delete the old backup file which has been created yesterday it could not, its not happening all the time and also could not simulate it.
It seems that sql server use the sql server backup files after completing backup process. is it possible ? if so how can i avoid this other than increasing the retention period of backup files ?
how can i verify that sql server relased the backup file for another process other than backup history tables ?
Its not failed due to anti virus scan and aslo confirmed not used by the another user or process by windows commnad attrib.
Can you let me know the other possiblites for the failure of the job?
October 1, 2010 at 12:15 pm
Did you ever find a solution to your problem? I am having the same problem. I have about 20 databases on the server. They all backup fine with the maintenance plan but when it comes to deleting files older than 1 day sometimes it works and sometimes it doesn't. When it fails the log states that one file was unable to be deleted. The weird thing is it's not the same file. One day the log will say: Database Database_ABC: Delete Old Backup Files...
Unable to delete file d:\MSSQL\BACKUP\Database_ABC\Database_ABC_db_dateinfo.BAK. 0 file(s) deleted.
and the next day it will be another database:
Database Database_DEF: Delete Old Backup Files...
Unable to delete file d:\MSSQL\BACKUP\Database_DEF\Database_DEF_db_dateinfo.BAK. 0 file(s) deleted.
Some days it workd fine.
October 1, 2010 at 12:20 pm
Oh wow- this is 4 years old...I do not remember what was the issue. As a point of interest, some time after this post, I converted all backups to Ola's backup, that can be found here:
-- Cory
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply