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