Maintenance Cleanup task not functioning properly?

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

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....

|||

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

|||

The trick is to replace .bak with *.*

It definitely works. :-)

|||

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?

|||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?!)
|||

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

|||

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

|||

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

|||

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

|||

-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/

|||

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!