Problem with Maintenance Plan to delete files older than 2 days

20 Dec 2005 5:58 AM

windsurfing_stew

Hi,

Hoping that someone in the group can shed some light on an issue I'm
having. We are trying to all delete backup files older than say two
days. I've tried a few methods, none of which have worked so far.

If I create a new Maintenance Plan and add a Maintenance Cleanup Task
and set it to delete all files in a given directory of a given
extension older than two days the plan executes successfully (including
the log) however no files are deleted.

If I look at the script produced by clicking the Script button I get:

EXECUTE master.dbo.xp_delete_file
0,N'G:\sql_back_full',N'bak',N'12/18/2005 16:55:21'

Now I'm in Australia and we express 18 Dec as 18/12/2005 rather than
12/18/2005. Not sure if that has anything to do with it.

I have searched google, google groups, msdn and microsoft forums. I
have turned up a handful of references to xp_delete_file. All of these
are to do with users asking how to use it or asking why it doesn't
work. No one has offered any answers.

Given the money we have layed down on this product I'm astounded that
this area hasn't been better tested or supported.

Any MVPs got any ideas, has anyone found a work around or does anyone
know anyone on the Development team who can shed some light on this?

Stew

20 Dec 2005 2:41 PM

urchin

Create a batch file that deletes everything in a directory (or a vbs
script or whatever)

Use xp_cmdshell to execute the batch file like so

declare @retcode int
execute @retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat"
select @retcode retcode

Your batch can take arguments or anything too, whatever works w/ your
installation

execute @retcode = master.dbo.xp_cmdshell
"\\machinename\folder\subfolder\test.bat " + convert(varchar,
getdate(), 112)

Just remember the execute is relative to the server so permissions all
that need to be set right.

Otherwise there are I'm sure 3rd party freeware utilities (or cheap pay
products) that monitor/delete/move/etc files in specified directories.
I've worked places where these were used.

23 Oct 2006 4:03 AM

N Raja

Hello. Here is the work around for this issue.

Instead of using 'Maintenance clean up task', insert a 'Execure T-SQL
statement task' and key in the SQL statement as follows
--------------------------------
declare @dt datetime

select @dt=getdate()-1 --No days to delete
EXECUTE master.dbo.xp_delete_file 0,N'D:\DumpDev',N'BAK',@dt
---------------------------------

This should do the trick..

Regards
N.Raja
MCT

*** Sent via Developersdex http://www.developersdex.com ***

23 Oct 2006 4:20 AM

N Raja

Hello

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

-------------------------------------------------

Regards

N.Raja

*** Sent via Developersdex http://www.developersdex.com ***