xp_delete_file

andyleonard — Wed, 11 Mar 2009 22:00:00 GMT

Introduction

I built a SQL Server 2008 Maintenance Plan and included a Maintenance Cleanup Task. For those who don't work with this stuff every day, Maintenance Plans are actually SSIS packages. The short version of a long story is: I couldn't execute the Maintenance Plan on the server. I won't go into that (not now, at least); but I noticed a View T-SQL button on the task:

When I clicked the button, I was presented with a textbox containing the following T-SQL:

EXECUTE master.dbo.xp_delete_file 0,N'E:\Database_Backups',N'bak','2009-03-07T16:45:17',1

I Don't Know Either

I said to myself: "Self, this could be a very useful extended stored procedure." So I popped open Books Online to look it up and... no topic found. Not a problem.

I popped over to MSDN and started scrolling through the online documentation and... no topic found. Not a problem.

I searched MSDN and... topics found. Yay! I clicked on a link to Connect about the topic missing from the documentation and... the link was broken. </HowIronic>

What I Do Know

I actually needed to clean up older backups, but I thought I could use this to also clean out old Team Foundation Server WSS (Windows SharePoint Server) logs that were filling up one of my drives. It turns out I could.

I edited and tested the following code:

declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -24, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))
EXECUTE master.dbo.xp_delete_file 0,N'E:\WSSLogs',N'log',@DeleteDate,1

It works. What's more, it runs at the same time as my nightly backup jobs (as a step in the same SQL Agent Job) and leaves my server fresh and clean for another day of pleasant computing.

Conclusion

I still do not know what the numeric arguments accomplish in the xp_delete_file extended stored procedure. But I was able to get some use from it nonetheless.

:{> Andy

Search results matching tags 'SQL Server', 'database developers', 'SQL Server 2008', and 'utilities'

xp_delete_file