August 23, 2011 at 12:49 pm
I have a maintenance plan set up to backup a database and includes a maintenance task to delete any bak file older than 1day. A really standard maintenance plan.
The database is backed up to the CIF share without any problems. The Maintenance Task to delete the file(s) even runs without error. Stating it was successful, but nothing is deleted.
Progress: Executing query "EXECUTE master.dbo.xp_delete_file 0,N'\\cifshare\s...". - 100 percent complete
Full Execute statement during debug
EXECUTE master.dbo.xp_delete_file 0,N'\\cifshare\sqlBackup\serverbackup_folder',N'bak',N'2011-08-23T13:45:44',1
I have checked all the folder permissions over and over again.
(They have to be right. I can log in to the server with the SQL Agent and delete files from the cif share.)
Deleted the Maintenance Plan and created new.
Debugged in BIDS without errors.
Other Notes: I have 10 other SQL servers backing up to the same CIF Share without problems.
Any ideas?
Quite frustrating as I am having to manually delete previous night's back-ups every morning.
August 23, 2011 at 12:55 pm
I seem to remember something about ending the path with a \... dont have the system in front of me to test with, but try changing the path to end with \
August 23, 2011 at 1:05 pm
I tried adding a "\" at the end of the Path but that did not work either. I seem to remember trying that at some point. I also tried using the "\" in combination with unchecking the Include first-level subfolders. This did not work either.
So strange...
August 23, 2011 at 1:15 pm
Is it by chance x64 and the disk a compressed volume?
you are certain that you have the extension as BAK, not ".BAK" correct? you are certain that the files you are testing with are old enough that they should be getting deleted?
August 23, 2011 at 1:17 pm
apparently others are working around the problem:
Instead of using 'Maintenance Cleanup Task', use 'Execute T-SQL
Statement Task' and key in the following SQL commands. This should fix
the error. This is an work around.
---------------------------------------------
declare @dt datetime
select @dt=getdate()-1 -- 1 is the files with 1 day old
EXECUTE master.dbo.xp_delete_file 0,N'D:\DumpDev',N'BAK',@dt
-------------------------------------------------
August 23, 2011 at 1:39 pm
I have backup files getting stored in its own individual directory for each database and I use below code to delete files older than 3 days. It works well.
DECLARE @OD datetime
DECLARE @OD_Am varchar(10)
SELECT @OD = Getdate() - 2
SELECT @OD_Am = CONVERT(varchar(10), @OD, 1)
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\master',N'bak',@OD_Am,1
GO
-- Just replace the master with each database directory name...
August 23, 2011 at 1:53 pm
Thanks all for the suggestions.
However, I question if it will work as I just tried to run the
EXECUTE master.dbo.xp_delete_file 0,N'\\cifshare\sqlBackup\Servername_folder\databasename_folder\',N'bak'
Nothing was touched even after I ran the execute statement without a date range. I'm puzzled at this point as to why it's not deleting anything, but still reporting a success.
August 23, 2011 at 1:58 pm
I recall looking into this once and there is a check subfolders ” check box that wasn’t checked in the clean-up task. That would only apply if you were backing up multiple databases to subfolders.
August 23, 2011 at 2:02 pm
Chuck Hottle (8/23/2011)
I recall looking into this once and there is a check subfolders ” check box that wasn’t checked in the clean-up task. That would only apply if you were backing up multiple databases to subfolders.
The box to check first-level subfolders is checked in my maintenance plan and has no effect. The databases are backed up to the correct directory, but not deleted.
August 23, 2011 at 2:09 pm
are the files you are testing with actual database backup files?
can you test on a local drive as opposed to network share?
Is the volume compressed?
The successful completion status means SQL Server THINKS it's doing what you asked of it... so if it's not deleting anything, it thinks there is nothing there which meet your criteria. My approach would be start as simple as possible and get it to work... start with an actual old database backup on a local disk. does that work? move that same file to the share, does it still work?
August 23, 2011 at 3:05 pm
NJ-DBA (8/23/2011)
are the files you are testing with actual database backup files?can you test on a local drive as opposed to network share?
Is the volume compressed?
The successful completion status means SQL Server THINKS it's doing what you asked of it... so if it's not deleting anything, it thinks there is nothing there which meet your criteria. My approach would be start as simple as possible and get it to work... start with an actual old database backup on a local disk. does that work? move that same file to the share, does it still work?
Our CIF shares are not compressed. I have pointed it to the local D drive and a different mapped drive that is not a CIF share. In all instances, it reports successful, but nothing is removed. Actual database backup files.
August 23, 2011 at 3:49 pm
I have seen a maintenance task not delete an old backup file that had been restored to a server after it had been deleted previously by the maintenance task.
How old are the files that aren't being deleted?
August 23, 2011 at 3:53 pm
Lynn Pettis (8/23/2011)
I have seen a maintenance task not delete an old backup file that had been restored to a server after it had been deleted previously by the maintenance task.How old are the files that aren't being deleted?
Most of the time the backup files are 1 day old. Today, while just trying to get it to delete everything or anything, the files range from less than a few minutes to a few hours old.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply