Ola Hallengren script not deleting old backup files but simply taking time 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 &lt;&gt; 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
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.