THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

xp_delete_file

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

Published Wednesday, March 11, 2009 7:00 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Matt Masson said:

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

March 11, 2009 8:46 PM
 

Andrew Kelly said:

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.

March 11, 2009 8:55 PM
 

MontyMole said:

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

March 12, 2009 9:06 AM
 

Joe Janhonen said:

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

March 12, 2009 10:21 AM
 

andyleonard said:

Thanks Matt, Andrew, and Monty!

  Now it's not so undocumented, is it?

:{> Andy

March 12, 2009 10:45 AM
 

Chris Wood said:

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

March 12, 2009 11:25 AM
 

steverich said:

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.

March 12, 2009 2:09 PM
 

eonsoy said:

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.

March 17, 2009 8:55 AM
 

ChrisLumnah said:

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.

March 20, 2009 9:50 AM
 

andyleonard said:

Great feedback folks!

  Chris, I like that approach a lot.

:{> Andy

March 20, 2009 9:58 AM
 

Jeff said:

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?

June 1, 2009 3:05 PM
 

David said:

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%!

July 2, 2009 6:30 AM
 

David said:

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.

July 3, 2009 12:03 AM
 

Brock said:

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

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

July 6, 2009 12:31 PM
 

Walter Brown said:

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?

September 17, 2009 2:58 PM
 

Manie Verster said:

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!

October 23, 2009 8:58 AM
 

Tim said:

To Walter Brown,

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

December 14, 2009 10:54 AM
 

Colin said:

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.

February 18, 2010 2:48 AM
 

Daniel Adeniji said:

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

March 31, 2010 8:06 PM
 

Bev said:

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?

April 15, 2010 11:21 AM
 

Rick Willemain said:

Very helpful ! Thanks

August 3, 2011 11:57 AM
 

Mab said:

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

September 22, 2011 6:19 AM
 

Mab said:

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

September 23, 2011 7:04 AM
 

jhouston said:

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.

February 27, 2012 2:13 PM
 

Pelser said:

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

April 17, 2012 4:38 AM
 

Pelser said:

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

April 18, 2012 4:16 AM
 

Liam North said:

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#

August 21, 2012 9:17 AM
 

Kevin Kunz said:

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

August 22, 2012 2:22 PM
 

Amit said:

Good resource

August 8, 2013 6:18 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement