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
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
Thanks Matt, Andrew, and Monty!
Now it’s not so undocumented, is it?
:{> Andy
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.
Great feedback folks!
Chris, I like that approach a lot.
:{> Andy
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
We have noticed before when there has to be enought free space on disk equal to the size of the largest file being deleted else the procedure runs without error but doesn’t do its work.
Because or MS taking away the server agent in the express versions I had to write a generic sp that gets called from the windows scheduler. Note the date format I use to pass to xp_delete_file:
ALTER PROCEDURE [dbo].[usp_daily_backups] @bakdrive VARCHAR(MAX),
@db_name VARCHAR(100),
@retention SMALLINT = NULL
AS
BEGIN
DECLARE @file_ext CHAR(4) = ‘.bak’
DECLARE @bak_name VARCHAR(100) = @db_name + REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),’-‘,”) + @file_ext
DECLARE @create_dir VARCHAR(500) = @bakdrive + @db_name
DECLARE @full_path VARCHAR(MAX) = @create_dir + ‘\’ + @bak_name
–###############################################
–## Make note of the backup with a start date ##
–###############################################
EXEC xp_create_subdir @create_dir
IF NOT EXISTS (SELECT d_backup_name FROM daily_backups WHERE d_backup_name = @bak_name)
INSERT INTO daily_backups VALUES (@db_name,@bak_name,GETDATE(),NULL)
–## Backup (Override)
BACKUP DATABASE @db_name TO DISK = @full_path WITH FORMAT
–## If succesful, update end date
IF @@ERROR = 0
UPDATE daily_backups SET d_dte_end = GETDATE() WHERE d_id = SCOPE_IDENTITY()
–##################################
–## Cleanup backups ##
–## Keep X days worth of backups ##
–## X will default to 3 ##
–##################################
SET @retention = CASE WHEN @retention IS NULL THEN 3 ELSE @retention END
DECLARE @del_dte VARCHAR(50) = CONVERT(VARCHAR,DATEADD(D,-(@retention),GETDATE()),127) –REPLACE(CONVERT(VARCHAR,GETDATE() – (@retention), 111), ‘/’, ‘-‘) + ‘T’ + CONVERT(VARCHAR,’00:00:00′,108)
EXECUTE xp_delete_file 0,@create_dir,N’bak’,@del_dte,1
END
Here is my final product which I will roll out to all our express sites. Wish I had time to explain the workings thereof but I’m sure the majority of you will know how to work with this one. I tried to make it as close as possible to the normal backup options. The generate script option will generate a script that will can be called from the windows task scheduler. (PS – Is there not a file attachment option in this site?):
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_daily_backups] @bakdrive VARCHAR(MAX),
@db_name VARCHAR(100), — Optional params (A = All dbs, U = All user dbs, S = All sys dbs)
@retention SMALLINT = NULL,
@generate_script SMALLINT = NULL
AS
BEGIN
DECLARE @str_len SMALLINT
DECLARE @last_char CHAR(1)
DECLARE @cmd VARCHAR(1000)
DECLARE @file_ext CHAR(4) = ‘.bak’
DECLARE @c_back_all_dbs CURSOR
DECLARE @bak_name VARCHAR(100)
DECLARE @create_dir VARCHAR(500)
DECLARE @full_path VARCHAR(MAX)
–###############################################
–## Make note of the backup with a start date ##
–###############################################
–## Take care of validating / correcting parameters first
EXEC xp_create_subdir @bakdrive
SET @str_len = LEN(@bakdrive)
IF SUBSTRING(@bakdrive,@str_len,1) != ‘\’
SET @bakdrive = RTRIM(@bakdrive) + ‘\’
IF @generate_script = 1
BEGIN
–## Generate backup script file
SET @cmd = ‘echo USE master > ‘ + RTRIM(@bakdrive) + ‘DailyDBBackup.sql’
EXEC master..xp_cmdshell @cmd
SET @cmd = ‘echo GO >> ‘ + RTRIM(@bakdrive) + ‘DailyDBBackup.sql’
EXEC master..xp_cmdshell @cmd
SET @cmd = ‘echo EXEC usp_daily_backups ”’+ @bakdrive + ”’,”’ + @db_name + ”’,”’ + RTRIM(CONVERT(CHAR(2),@retention)) + ”’ >> ‘ + RTRIM(@bakdrive) + ‘DailyDBBackup.sql’
EXEC master..xp_cmdshell @cmd
END
–## Create an audit table on master database
IF OBJECT_ID(‘master..daily_backups’) IS NULL
CREATE TABLE daily_backups
(
d_id INT IDENTITY(1,1) NOT NULL
,d_db_name SYSNAME
,d_backup_name VARCHAR(100)
,d_dte_start SMALLDATETIME
,d_dte_end SMALLDATETIME
CONSTRAINT pk_daily_backups PRIMARY KEY
(
d_id ASC
)
)
IF OBJECT_ID(‘TempDB..#backup_dbs’) IS NOT NULL
DROP TABLE #backup_dbs
CREATE TABLE #backup_dbs (name SYSNAME)
IF @db_name = ‘A’
INSERT INTO #backup_dbs
SELECT name
FROM sys.databases
IF @db_name = ‘U’
INSERT INTO #backup_dbs
SELECT name
FROM sys.databases
WHERE database_id > 4
IF @db_name = ‘S’
INSERT INTO #backup_dbs
SELECT name
FROM sys.databases
WHERE database_id <= 4
IF @db_name NOT IN (‘A’,’U’,’S’)
INSERT INTO #backup_dbs
SELECT name
FROM sys.databases
WHERE name = @db_name
SET @c_back_all_dbs = CURSOR FOR
SELECT name
FROM #backup_dbs
WHERE name NOT LIKE ‘%temp%’
OPEN @c_back_all_dbs
FETCH NEXT FROM @c_back_all_dbs INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @bak_name = @db_name + ‘_’ + REPLACE(CONVERT(VARCHAR(10),GETDATE(),120),’-‘,”) + @file_ext
SET @create_dir = @bakdrive + @db_name
SET @full_path = @create_dir + ‘\’ + @bak_name
–## Make sure the sub directory for database exist
EXEC xp_create_subdir @create_dir
IF NOT EXISTS (SELECT d_backup_name FROM daily_backups WHERE d_backup_name = @bak_name)
INSERT INTO daily_backups VALUES (@db_name,@bak_name,GETDATE(),NULL)
–## Backup (Override)
BACKUP DATABASE @db_name TO DISK = @full_path WITH FORMAT
–## If succesful, update end date
IF @@ERROR = 0
UPDATE daily_backups SET d_dte_end = GETDATE() WHERE d_id = SCOPE_IDENTITY()
–##################################
–## Cleanup backups ##
–## Keep X days worth of backups ##
–## X will default to 3 ##
–##################################
SET @retention = CASE WHEN @retention IS NULL THEN 3 ELSE @retention END
DECLARE @del_dte VARCHAR(50) = CONVERT(VARCHAR,DATEADD(D,-(@retention),GETDATE()),127)
EXECUTE xp_delete_file 0,@create_dir,N’bak’,@del_dte,1
FETCH NEXT FROM @c_back_all_dbs INTO @db_name
END
CLOSE @c_back_all_dbs
DEALLOCATE @c_back_all_dbs
END
With regard to the Powershell solution supplied by David, it needs a minor change to work due to a bug with SQL server mistakingly recognising the Powershell cmdlet $(Get-Date) as the TSQL DATE and because it is prefixed with $, SQL Server thinks that an escape token is required. See this Connect log: http://connect.microsoft.com/SQLServer/feedback/details/613673/qql-agent-job-powershell-step-giving-escape-token-error#
the first parameter in
EXECUTE master.dbo.xp_delete_file 1,’D:\SQLData\Instances\MAINTENANCE’,’TXT’,@expiration_log,1
is FileTypeSelected its value can be (0) FileBackup and (1) FileReport
Good resource
How about a nice one-liner you add to a SQL Agent job — or xp_cmdshell to delete whatever you need (-14 is the number of days back to delete, change the file path to the path of your files, and *.txt to *.bak–whatever you’re trying to delete):
cmd /c "forfiles /p "[file path…]" /s /m *.txt /d -14 /c "cmd /c del @path""&IF %ERRORLEVEL% EQU 1 EXIT /B 0
I’ve been searching for an answer for this myself. FINALLY, I designed the following single line of code that actually works. It returns a FORFILES exit code 0 (success) when %ERRORLEVEL% is a 0 or a 1. Nesting the CMD.EXE commands allows me to capture the resulting FORFILES exit code in a **single line of code**–which is what I personally needed.
*"ERROR: No files found with the specified search criteria"* is an exit code of 1.
Exit codes greater than 1 will be returned as an error–making "No files found" more like a default behavior than an error.
p.s. I’m finding that with FORFILES other solutions might get rid of the actual error text but are ignoring the %ERRORLEVEL% which signals a fail in my application. AND I legitimately want %ERRORLEVEL% of 2 or more to be returned as an error–rather than being masked by some arbitrary successful command concatenated onto the end of FORFILES.
p.s. the problem with xp_delete_file is it actually checks the header of the file to see if it was created by the actual an actual SQL Maintenance job. To make matter’s worse, it’s actually version specific. So if you upgrade your SQL Server there’s a chance that some older files will never be purged.
I find this behavior extremely unhelpful that you can actually create SQL Maintenance Tasks in SSIS for re-indexing and integrity checks and log them to files with SSIS and NOT actually be able to delete SSIS logging of such tasks with SQL Cleanup Maintenance Tasks in SSIS.
Plus, if you research the Microsoft KBs you’ll find that xp_delete_files default behavior has been broken in the past and need to be patched to work properly. If it’s that "undocumented" maybe Microsoft should consider staying away from it and use another command for Maintenance Cleanup Tasks altogether.