xp_delete_file

First arg is

0 - specifies a backup file

1 - specifies a report file

I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files you're deleting with the third argument.

Fifth argument is whether to delete recursively.

0 - don't delete recursively (default)

1 - delete files in sub directories

xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is not documented by MS. Just be careful that it will not delete just any file type.

You can do a similar thing with the History Cleanup Task.

declare @dt datetime

set @dt = dateadd(m,-3,getdate())

EXEC sp_delete_backuphistory @oldest_date=@dt

EXEC sp_purge_jobhistory @oldest_date=@dt

EXEC sp_maintplan_delete_log @oldest_time=@dt

EXEC sysmail_delete_mailitems_sp @sent_before=@dt

There appears to be a reasonably good summary of the syntax here: http://nikeshikari.blogspot.com/2008/05/tech-mssql-xpdeletefile.html

As I discovered in SQL2005 SP2 xp_delete_file can only run if you have sysadmin authority. Not sure if this changed in SP3 or SQL2008.

Chris

Recently I found that the xp_delete_file does not delete Quest LiteSpeed backup files. Apparently, the proc checks the file, not just the extension, to ensure it is a SQL native backup.

I'd like to share the following KB with you guys:

http://support.microsoft.com/kb/938085

I found this KB when I wanted to use this XP to delete logs which are created by some Maintenance Plans.

I actually took this one step further and wrote my own delete function.

Ideally, you do not want to delete any backup file that has not been moved to tape.  Since backup software will generally turn the archive bit off when it has been moved, I used this as my key.  I wrote an ssis package, that enumerates a folder into a for each loop.  For each file that I have, i check the age of the file (in our case 2 days is all we want to keep on disk) and if the file archive is bit archive is true.  If the archive bit has been cleared and the file is 2 days or older, i delete the file.  If the file is not old enough, I leave it alone.  If the file is 2 days or older, and the archive bit has not been cleared, I email myself a message.  This at least tells me that the tape backup process has failed in the couple of runs.

I was wanting help with doing what ChrisLumnah did. I know very little about writing T-SQL to get this accomplished. Does anyone have the code?

I'm tired of Maintenance plan!!! Since SQL 7 they have been troublesome at best.

Since we're talking SQL2008, we may as well talk Powershell. ;-).  It's quite likely that SQL2008 will be installed on a Windows 2008 server and both of these products support Powershell natively.  And since deleting text files or backup files in the OS is not so much a SQL function, but more an OS function, let PS do the work.  One line of code, saved as a .ps1.  Call the .ps1 file from a SQL Agent job.  Done and dusted.  

PS code:

Get-Item "I:\Microsoft SQL Server\MSSQL.3\MSSQL\LOG" -recurse -include *.txt -exclude *dump*.txt | Where {($_.CreationTime -le $(Get-Date).AddDays(-30))} | Remove-Item -Force

Save this as a .ps1 file from notepad.

Call the ps1 file in a job as follows:

"C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe" "D:\Powershell\RemoveOldLogs.ps1"

This job step will be of type Operating System(CmdExec).  

Works 100%!

Sorry, the above should be Get-ChildItem.  Get-Item can't have the -recurse switch.

I also called the above .ps1 file from an SSIS package.  Create an Execute Process task.  The executable is the path and name of powershell.exe.  The argument is the path and name of the .ps1 file.

This can be used to get the @DeleteDate in one statement.

set @DeleteDate = CONVERT(nvarchar(19),DateAdd(hh, -24, GetDate()),126)

I am having a similar issue with xp_delete_file.

It does not recognize the file header of RedGate SQL Backup Files as backups and skips them.  So, I wonder if there is a workaround.  Is there another stored procedure that anyone knows up that will delete a file of any file type that could be executed instead of xp_delete_file?

Andy, this is what make it worthwhile to be a database administrator. You battle to get something right, search the net and voila! here is an article that helps! Thanks man!

To Walter Brown,

How about using something like xp_cmdshell 'DEL d:\backups\*.bak'

Thanks for the script Andy, it helped me out a lot. Just a note: The deletion of the files only works on a single level down from the folder that you specified. So for example if you have a folder structure such as D:\Backups\Random Folder\Another Folder, then the script will only delete files in the "Backups" and "Random Folder" folders.

Colin:

As the folder name (D:\Backups\Random Folder\Another Folder) as special chracters in it, did you try enclosing in double-quotes such as

EXECUTE master.dbo.xp_delete_file 0

     ,N'"E:\WSSLogs\Contains spaces "'

     ,N'log'

     ,@DeleteDate

     ,1

Monty - RE: You can do a similar thing with the History Cleanup Task.

declare @dt datetime

set @dt = dateadd(m,-3,getdate())

EXEC sp_delete_backuphistory @oldest_date=@dt

How would I drop audittrace*.trc files caused by C2 settings?

Very helpful ! Thanks

Hi, I was trying to use xp_delete_file in one of my stored procedure. I was tryign to pass all the parameters like folderPath, FileType and Date to delete the files from specific folder. If I put FileType and Date as parameter with xp_delete_file, its works like

EXECUTE master.dbo.xp_delete_file 0,N'C:\Test\',@fileType,@cutoffDate,0

But if I try

EXECUTE master.dbo.xp_delete_file 0,@folderPath,@fileType,@cutoffDate,0

I get the error Msg 22049, Level 15, State 0, Line 0

Error executing extended stored procedure: Invalid Parameter

Any help will be appriciated

I figured out what the problem was. The variables which I was declaring had the datatype NVARCHAR, where I should have delcare them as VARCHAR.

Thanks