1

I have a job that deletes transaction log backups older than 3 days.

if for some reason there are no files to delete, it comes out with the following error message:

Msg 22049, Sev 16, State 1: xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]
Job 'Log Shipping - Delete tran backups older than 3 days' : Step 1, 'delete the trn files older than 3 days' : Began Executing 2019-01-18 23:00:02

Msg 22049, Sev 16, State 1: xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000]

this is the code of the job:

-This will set the date time stamp to pass onto the Stored Proc for 72 hours from the current date when it runs
--The stored proc will recursively delete all TRN files from the parent level specified all the way down
declare @DeleteDate nvarchar(50)
declare @DeleteDateTime datetime
set @DeleteDateTime = DateAdd(hh, -72, GetDate())
set @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'\\homer\S_SYSTEMS\FullBackups\MY_SERVER\_log1\',N'trn', @DeleteDate,1

--5th argument below "0 - don't delete recursively (default)" and  "1 - delete files in sub directories"
EXECUTE master.dbo.xp_delete_file 0,N'\\homer\S_SYSTEMS\FullBackups\MY_SERVER\_log12',N'trn', @DeleteDate,1

Here is the job: enter image description here

how can we change this? if there are no files, that's fine, no need to fail the job.

  • 1
    I would consider doing this from Powershell or some other OS-level approach rather than have SQL Server access the file system any more than it has to. Like string handling, this just isn't its forte... – Aaron Bertrand Jan 19 at 16:18
0

I managed to get the same error when executing the query on a random chosen folder (that was empty).

Message
Executed as user: DOMAIN\ServiceAccount. xp_delete_file() returned error 2, 'The system cannot find the file specified.' [SQLSTATE 42000] (Error 22049).  The step failed.

At first, I wanted to see if we could use aTRY CATCH Block to 'muffle' the sound of the error screams our SQL Server is throwing at us.

It still fails with the same error message, so we cannot use that to get a 'false positive'.

I do think that the error is happening because either

  1. The folder does not exist
  2. The service account does not have permissions on the folder

When executing on a folder where i knew that my service account did not have permissions on, i got the same error. After changing the folder, and adding my service account as 'full control' on the folder, it worked, even if it was empty.

enter image description here

Permissions on the folder for my service account

enter image description here

The folder is empty

enter image description here

Checking the job T-SQL

enter image description here

Looks good

Message

Executed as user: DOMAIN\ServiceAccount. The step succeeded.

This was tested on SQL Server 2014 (SP2-CU12)

There could be better alternatives for the proc, or other workarounds.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.