locked
Maintenance Cleanup task not functioning properly??? RRS feed

  • Question

  • Using SQL Server 2005 Management Studio and backing up databases. When I add the task Maintenance Cleanup to remove database backups older then 1 day the task fails???
    What part of this TSQL code does SQL Server 2005 Management Studio not understand?

    EXECUTE master.dbo.xp_delete_file 0,N'\\pdxsql03\SQL Backup\POSSQL_Server',N'.bak',N'05/15/2006 08:09:37'

    The database file backup extension is .bak and the file is older than one day which was specified.
    It is just not deleting the file.
    We have deleted the maintenance task and recreated it with no effect and this has been happening for over a month.
    Any help here would be greatly appreciated.

    Carl
    carlt@gicw.org



    Tuesday, May 16, 2006 3:21 PM

Answers

  • it works for me only with "BAK" for the extension without "."

    and with "\" at the end of the path.

     

    I thought next Service Release should take care about this issue.

    • Proposed as answer by jatdesi Thursday, April 2, 2009 7:03 PM
    • Marked as answer by Kalman TothEditor Saturday, November 21, 2009 4:21 AM
    Monday, October 6, 2008 8:15 AM
  • My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

    declare @dt datetime

    select @dt=getdate()-5
    EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@dt,1

    The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

    Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement 


    I have SP3 installed and no matter what I've tried (tried it all on this forum) it will not delete anything and does not provide any errors, only success.

    So, I tried created my own custom T-SQL using the code provided above, and it worked and still works perfectly!

    Thanks!
    Wednesday, September 30, 2009 4:18 PM
  • There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

    If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

     

    The patching procedure is a little complicated. Read this:

     

    http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

    Monday, April 30, 2007 6:07 PM
  •  

    I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

     

    I finally just got the T-SQL statement and created my own job.

    This is the original statement:

     

    EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

     

    Is simply added a 1 to the end of the statement and made it:

     

    EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

     

    This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

    Thursday, August 30, 2007 5:48 PM
  • For other folks who might run across this thread...  I was having this same problem and after a week of researching I have found several possible solutions... I didn't try all of these as I finally found a combination that worked for me.. some of them might not make any sense at all, but after a week I was game to try anything:

     

    - no "." in front of "bak" or "trn"

    - use a \ at the end of the path (this is the one that got me working)

    - use * for the extension

    - switch between caps & lower case (BAK vs bak vs Bak)

    - switch on first level folders

    - the task checks the files to verify that they are backup files... so empty test files may not work.

    - there are several script options out there...  VBScript, unix utils, & others.

     

    Hopefully this will help someone save some time and frustration!

    Friday, February 15, 2008 2:27 PM

All replies

  • I've been having the same problem for over three months now and keep checking back here to see if there's been a fix....

     

    Tuesday, May 16, 2006 7:19 PM
  • http://blogs.msdn.com/sanchan/archive/2006/04/23/581640.aspx

     

    This is addressed in SP1. Please look at the above blog for more information.

    Gops Dwarak

    Wednesday, May 24, 2006 5:50 AM
  • The trick is to replace .bak with *.*

    It definitely works. :-)

     

    Thursday, November 23, 2006 9:02 AM
  • I tried to replace the extension "bak" with "*.*", "*" and ".*" I know this looks like the meeting of the disabled Smilie group, but you know what I mean. It definitely _DOESN´T_ delete any backups. However, when I execute the t-sql created by the maintenance Cleanup Task, it deletes the files.

    Any suggestions?

    Friday, November 24, 2006 9:05 AM
  • Not working for me either. SP1 applied and all. Subdirectories for backups. .. trying to delete archived backups, in the form Filename.bak.7z. No worky worky!
    Please advise? (Read: How hard is it to implement a functioning XSP?!)
    Monday, December 4, 2006 9:09 PM
  • My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

    declare @dt datetime

    select @dt=getdate()-5
    EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@dt,1

    The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

    Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement 

    Tuesday, January 9, 2007 9:39 AM
  • The workaround above may or may not work, depending on what the problem is.

    To get to the bottom of the problem - what is the exact error that you are getting? We can then search if this is a known problem. There were number of issues with Cleanup task. A few were fixed in SP1, like Gops has mentioned, more were fixed in SP2 which is in the CTP release right now. If you can, please install SQL Server 2005 SP2 CTP December from here: http://www.microsoft.com/sql/ctp.mspx and let us know if the problem sitll exists.

    Regards,

    Maciek Sarnowicz

    Wednesday, January 17, 2007 9:03 PM
  • I tried to load up SP2 (thought I had already) to test this with it...

     

    well, I show I am running SP1 and though I tried to update I could not get everything to update.  Either it had a newer version (according to the updater) or I was told that components must be updated together. 

     

    SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

     

    select @@version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

     

    So now the question is how do I even get it to upgrade to SP2?

     

    Thanks,

     

    -lance

    Sunday, April 22, 2007 2:22 PM
  • There is an "off by one" bug using the time unit of measure in "Maintenance" and "Cleanup" tasks.

    If you select "Weeks" you get "Days", with "Days" you get "Hours", and "Hours" blows the index.

     

    The patching procedure is a little complicated. Read this:

     

    http://blogs.msdn.com/psssql/archive/2007/04/06/post-sql-server-2005-service-pack-2-sp2-fixes-explained.aspx

    Monday, April 30, 2007 6:07 PM
  •  -lance wrote:

    I tried to load up SP2 (thought I had already) to test this with it...

     

    well, I show I am running SP1 and though I tried to update I could not get everything to update.  Either it had a newer version (according to the updater) or I was told that components must be updated together. 

     

    SELECT SERVERPROPERTY('ProductVersion') = 9.00.3054.00

     

    select @@version = Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

     

    So now the question is how do I even get it to upgrade to SP2?

     

    You are running SQL Server 2005 Service Pack 2 (9.00.3054.00).

    You are running Windows Server 2003 Service Pack 1.

     

    Hope this clears up your confusion.

     

    Paul A. Mestemaker II

    Program Manager

    Microsoft SQL Server Manageability

    http://blogs.msdn.com/sqlrem/

    Friday, May 4, 2007 1:53 AM
  •  

    I was also having a similar issue. First the cleanup task would just not delete any files no matter where I pointed it to. I originally had put “.trn” in the file extension box. I then removed the . and everything deleted correctly. The problem we then ran into was that it would only delete from the top level folder that we pointed to. In another blog I read that I must check the Include first-level subfolders box to delete from these sub folders. The problem is that this checkbox is nowhere to be found on the maintenance cleanup task dialog box. We do have service pack 2 loaded and most of the updates but….?

     

    I finally just got the T-SQL statement and created my own job.

    This is the original statement:

     

    EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18'

     

    Is simply added a 1 to the end of the statement and made it:

     

    EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\backup',N'trn',N'08/25/2007 13:49:18',1

     

    This job runs great and deletes all the files on level below the top level folder. I did try adding a 2 to see if it would let me delete further levels but it errors out. Hope this helps somebody!

    Thursday, August 30, 2007 5:48 PM

  • I experienced this same problem for a few days. My solution was to create two separate Maintenance Plans, one for the .bak and one for .trn

    I previously tried to combine the two extensions into one job by specifying: 'trn, bak' also '.trn, .bak' also '*.trn, *.bak' and finally '*.*'   --- needless to say non of these worked.

    When creating the individual jobs I specified only 'trn' as my file extension. Ran the job and it worked perfectly. The second job has only 'bak' and this to works perfectly too.

    Oh, checking the 'Include first-level subfolders' text box is a necessity when backing up your databases to separate folders.

    Cheers,
    Damian Coverly.

    Tuesday, October 9, 2007 10:09 AM
  • Yeah!!!  I finally got it to work.  I removed the period at the beginning of the file extension.  So in file extension, I only have "Bak". 

     

    Thanks for your help!

     

    Jason

    Texas Mortgage Broker

     

    Tuesday, November 6, 2007 6:29 PM
  • For other folks who might run across this thread...  I was having this same problem and after a week of researching I have found several possible solutions... I didn't try all of these as I finally found a combination that worked for me.. some of them might not make any sense at all, but after a week I was game to try anything:

     

    - no "." in front of "bak" or "trn"

    - use a \ at the end of the path (this is the one that got me working)

    - use * for the extension

    - switch between caps & lower case (BAK vs bak vs Bak)

    - switch on first level folders

    - the task checks the files to verify that they are backup files... so empty test files may not work.

    - there are several script options out there...  VBScript, unix utils, & others.

     

    Hopefully this will help someone save some time and frustration!

    Friday, February 15, 2008 2:27 PM
  •  

    - use a \ at the end of the path (this is the one that got me working)

     

    This worked for me also thanks crenstom

    Thursday, April 24, 2008 5:12 AM
  • Thanks. I added *.BAK for the extension and that seem to work fine.

     

    Original Error:

     

    Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'',N'',N'2008-08-05T16:44:11'" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

     

    Working T-SQL after editing original plan:

     

    EXECUTE master.dbo.xp_delete_file 0,N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\',N'*.BAK',N'2008-08-05T16:52:28',1

     

     

    Tuesday, August 12, 2008 8:53 PM
  • it works for me only with "BAK" for the extension without "."

    and with "\" at the end of the path.

     

    I thought next Service Release should take care about this issue.

    • Proposed as answer by jatdesi Thursday, April 2, 2009 7:03 PM
    • Marked as answer by Kalman TothEditor Saturday, November 21, 2009 4:21 AM
    Monday, October 6, 2008 8:15 AM
  • newbie guess - make sure that the sql server agent account has delete rights to the folder where the .bak files are kept.
    Thursday, April 2, 2009 7:05 PM
  • If nothing works then follow the steps below.
    1. Create the following stored procedure.
    CREATE PROCEDURE dbo.USP_BACKUPCLEANUPTASK
    @dbname as varchar(400),
    @type as char(1),
    @Days as int
    AS
    DECLARE @fileid AS NVARCHAR(1000)
    DECLARE @var3 AS VARCHAR(300)
    DECLARE @result AS INT
    DECLARE BACKUP_DROP CURSOR FOR
    SELECT a.physical_device_name FROM msdb.dbo.backupmediafamily a
    LEFT OUTER JOIN msdb.dbo.backupset b
    ON a.media_set_id = b.media_set_id
    WHERE b.database_name = @dbname
    AND b.type = @type
    AND CONVERT(VARCHAR(10),b.backup_finish_date,110) BETWEEN  CONVERT(VARCHAR(10),GETDATE()-99,110)
    AND CONVERT(VARCHAR(10),GETDATE()- @Days,110) 

    OPEN BACKUP_DROP

    FETCH NEXT FROM BACKUP_DROP
    INTO @fileid

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @var3 = 'del '+ @fileid
    EXEC @result = master.dbo.xp_cmdshell @var3
    IF (@result = 0)
    BEGIN
    PRINT 'Success'
    PRINT+@fileid
    END
    ELSE
    BEGIN
    PRINT 'Failure'
    PRINT+@fileid
    END
    FETCH NEXT FROM BACKUP_DROP
    INTO @fileid
    END
    CLOSE BACKUP_DROP
    DEALLOCATE BACKUP_DROP

    2. Execute the Procedure
    EXEC dbo.USP_BACKUPCLEANUPTASK '<DBNAME>', '<BACKUPTYPE>',<RETENTION PERIOD>
    GO

    3. Following is the example for database XXX with retention period of 2 days for FULL BACKUP
    EXEC dbo.USP_BACKUPCLEANUPTASK 'XXX', 'D',2
    GO

    Wednesday, April 22, 2009 7:55 AM
  • My solution was to take the T-SQL created by the cleanup task and modifying it slightly:

    declare @dt datetime

    select @dt=getdate()-5
    EXECUTE master.dbo.xp_delete_file 0,N'E:\Backups',N'BAK',@dt,1

    The variable is used to check the age of the backup-files. In this case, all backups older than 5 days are deleted. The last Number (=1) gives the number of subdirectories to be searched in for .bak-files. AFAIK a maximum of 5 subdirs is supported.

    Then you can take it and put it into an "execute SQL"-Task, take the package and schedule it or directly schedule it in the Job Scheduler of Management Studio as SQL-Statement 


    I have SP3 installed and no matter what I've tried (tried it all on this forum) it will not delete anything and does not provide any errors, only success.

    So, I tried created my own custom T-SQL using the code provided above, and it worked and still works perfectly!

    Thanks!
    Wednesday, September 30, 2009 4:18 PM
  • This is still an issue with SQL 2008.  Adding the file extension without the "." and adding the "\" to the end of the path worked.

    Thank you

    Wednesday, March 31, 2010 9:50 AM
  • This is still an issue with SQL 2008. I am trying to delete zip files as I zip the backup files. I used all the recommendations used in the thread, with no results

    I even tried xp_delete_file and still with the same issue.

    Thanks!

    Monday, May 10, 2010 10:22 PM
  • I can't get it to delete zip files either with SQL Server 2008. It doesn't have any trouble with bak or trn deletions, but that stored proc seems to behave differently when it encounters a zip file.
    Monday, May 17, 2010 6:07 AM
  • For other folks who might run across this thread...  I was having this same problem and after a week of researching I have found several possible solutions... I didn't try all of these as I finally found a combination that worked for me.. some of them might not make any sense at all, but after a week I was game to try anything:

     

    - no "." in front of "bak" or "trn"

    - use a \ at the end of the path (this is the one that got me working)

    - use * for the extension

    - switch between caps & lower case (BAK vs bak vs Bak)

    - switch on first level folders

    - the task checks the files to verify that they are backup files... so empty test files may not work.

    - there are several script options out there...  VBScript, unix utils, & others.

     

    Hopefully this will help someone save some time and frustration!


    Thank you! .. putting the "\" at the end of the path fixed mine as well .. I have been looking for the solution for a week! I can't believe it was so simple. Thanks again.
    Saturday, September 4, 2010 9:04 PM
  • I know this is an old thread but since we still administrate and maintain some old SQL 2005 servers out there, just sharing my 2 cents hint:

    - In my case the task was simply disabled. (Grayed out)
    So right click the task of your maintenance plan and make sure it is enabled !

    Tuesday, October 23, 2012 3:48 PM