SQL Server xp_delete_file not deleting files

Question

I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days.

Here's what I've got that's not working:

DECLARE @DateString CHAR(8)
SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1)
EXECUTE master.dbo.xp_delete_file 0, 
                  N'e:\Database Backups',N'7z', @DateString, 1

I've also tried changing the '1' a the end to a '0'.

This returns 'success', but the files aren't getting deleted.

I'm using SQL Server 2005, Standard, w/SP2

Answer 1

I had read many different approaches and solutions multiple individuals pursued when attempting to resolve the issue with the extended stored procedure xp_delete. The solutions are:

1. Be sure to NOT have a period (.) in the extension when configuring the SSIS maintenance task.
2. Be sure to click on the Include First-Level sub folders if they exist for each database backup.
3. Be sure to click on the backup files at the top. The maintenance task does check the file type. For database backups, I believe it checks the backup file header.

In my scenario, all of the above were correct. There are few comments on the web where some of said the routine xp_delete is buggy.

When the backup files were not being deleted, I extracted the SQL for the maintenance and ran it from SSMS. The resulting message was the file was not a sql server backup file. This message was erroneous as the backup could be restored successfully, resulting in an operational database.

The database commands used to verify the database were:

RESTORE HEADERONLY FROM DISK = N'<file path\filename>.Bak'
RESTORE VERIFYONLY FROM DISK = N'<file path\filename>.bak'

Both of the above commands indicated the backup file was valid.

Next I opened the event viewer and found messages indicating there were login errors for the connection manager. This was strange because I had validated the connection with the test connection button. The errors were not related to any account I had created.

Event Viewer Message:

*The description for Event ID 17052 from source MS SQL SERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer. If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event:

Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'domain\servername$'.*

Next I logged onto a machine where xp_delete was functioning correctly. After reviewing the active directory and not finding the system account, I proceeded to the event viewer to find similar messages. Here it became evident the account for domain\server$ is mapped to system security.

Next step was to compare the database security where xp_delete worked against the database where it did not work. There were 2 missing logins under security in the database where xp_delete did not work. The 2 missing logins were: NT AUTHORITY\SYSTEM NT Service\MSSQLSERVER

After adding NT service\MSSQLSERVER, xp_delete successfully worked.

One approach to testing is to use the maintenance task to delete an individual file.