SQL Server – xp_delete_file – error 2

Code


DECLARE @currentdate datetime
DECLARE @olddate datetime

set @currentdate = CURRENT_TIMESTAMP

--Change
set @olddate = @currentdate - 1
--Change number here depending how many days of backup file you
--want to retain on server.

-- Add below line for each database/path you might need.
EXECUTE master.dbo.xp_delete_file
            0
            ,N'C:\MSSQL\Backup\Temp2\'
            ,N'bak'
            ,@olddate
            ,1

For some reason, master.dbo.xp_delete_file sometimes returns the error listed below:

Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'

To remediate:

  1. Ensure that the folder referenced in the master.dbo.xp_delete_file exists
  2. Ensure that the SQL Server or SQL Server Agent has access to the folder referenced

If still unable to fix:

  1. Enable NTFS \ Object level auditing
  2. Use OS Monitoring tools.  As such tools gather & display a lot of data filter for File System Operation & your specific folder

 

References

  1. Andy Leonard – xp_delete_file
    http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s