2

We recently migrated to a new SQL Server and the SQL Services are running under a Service Account. I did observe that the Service Account do not have FULL rights on G drive and the old files are not being cleaned up.

Question: Why doesn't the xp_delete_file procedure Error out when it cannot delete a file OR simply move on instead of waiting for around 9.5 minutes?

Also, doesn't the job error if it cannot delete the old backups? We will not know until we receive LOW DISK SPACE alert !!

Here is the log details.

Date and time: 2017-05-23 02:02:17
Command: BACKUP LOG [DBName] TO DISK = N'G:\SQLBackups\ServerName\DBName\LOG\ServerName_DBName_LOG_20170523_020217.trn' WITH CHECKSUM, COMPRESSION
Processed 17 pages for database 'DBName', file 'DBFileName_Log' on file 1.
BACKUP LOG successfully processed 17 pages in 1.206 seconds (0.110 MB/sec).
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2017-05-23 02:02:18

Date and time: 2017-05-23 02:02:18
Command: RESTORE VERIFYONLY FROM DISK = N'G:\SQLBackups\ServerName\DBName\LOG\ServerName_DBName_LOG_20170523_020217.trn' WITH CHECKSUM
The backup set on file 1 is valid.
Outcome: Succeeded
Duration: 00:00:02
Date and time: 2017-05-23 02:02:20

Date and time: 2017-05-23 02:02:20
Command: DECLARE @ReturnCode int EXECUTE @ReturnCode = [master].dbo.xp_delete_file 0, N'G:\SQLBackups\ServerName\DBName\LOG', 'trn', '2017-05-16T02:02:20' IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
Outcome: Succeeded
Duration: 00:09:28
Date and time: 2017-05-23 02:11:48

P.S. The Service Account was granted FULL rights and the old files are being cleaned up.

Version Info: SQL Server 2012 SP3 CU8 / Windows Server 2012

5
  • Ultimately it means that the xp_delete_file is returning with error code 0, meaning it has run successfully. Have you tried running it manually in a query window, or a command prompt? May 23 '17 at 10:28
  • It has run successfully but the files had not been deleted...Running it in query window might work because it runs under my account. The problem here is xp_delete_file taking 9.5 minutes when it cannot delete a file when running under Service Account without FULL access to G drive. May 23 '17 at 11:31
  • I'm unsure. I'd say try running in a query window and a CMD window to try and debug. Your issue I suppose is that this is an undocumented, unsupported procedure. You could also contact Ola? ola.hallengren.com/contact.html May 24 '17 at 9:29
  • xp_delete_file - Undocumented command!!. xp_delete_file is used in Maintenance Plans....View T-SQL in Maintenance Cleanup Task gives me EXECUTE master.dbo.xp_delete_file 0,N'F:\DatabaseBackup',N'bak',N'2017-05-17T08:30:29',1 (on a different server that still doesn't use Ola's Maintenance solution) May 24 '17 at 13:32
  • I meant to convey that, even xp_delete_file is used by folks at Microsoft!! May 24 '17 at 14:15
1

You're providing xp_delete_file with a directory, and a file extension to check for. How many files were there that needed to be deleted when this took 9.5 minutes to run?

It would make sense that the process would be trying to delete each file, and would ultimately fail to delete each file. If there's one file to try to delete, it would complete much faster than if there's 100.

To try to confirm this, I created a directory in which I placed (initially) 1 .bak file dated 2017-03-01, set it to "read-only" (so it shouldn't get deleted), and ran the following to try to delete it.

EXECUTE master.dbo.xp_delete_file 0,N'H:\Dummy_Backup',N'bak',N'2017-04-27T14:09:10'

With 1 file, the command completed successfully (but no files were deleted) in less than 1 second.

I made copies of the file, so I had 64 files, and ran again. This time, it took 1 second.

Continuing in the same vein:

  • 256 files - 5 seconds
  • 512 files - 15 seconds
  • 1024 files - 31 seconds

So, it seems likely that there were a large number of files it was trying (and failing) to delete.

And, to be clear - it failed to delete any files in all of the above tests as well. I went in and turned off the "read-only" attribute for the 1024 files after the last test above, and ran it again - all 1024 files were deleted successfully (in 25 seconds).

So, it does seem likely that the 9.5 minute wait was due to a large number of files it was trying to delete.

2
  • The files were piled up from 5/6 to 5/16. Around 48 files per day, so total number of files were around 500 to 525. File size total would be 12 to 15GB. May 26 '17 at 9:50
  • Looping 500 files to simply check whether it can delete or not due to insufficient permissions ever take 9.5 minutes? It could have simply checked for Folder permissions and quit instead of looping 500 files. Looks like time to switch to PowerShell !! May 26 '17 at 9:52

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.