Maintenance plan maintenance cleanup task running successfully, but not deleting files?

This has been driving me crazy. I have tried a开发者_如何学Goll suggestions and no go.

This absolutely does not work. The files are all still there.

The job runs successfully, but the files do not delete.

---

I recently ran into the same problem, and it was due to folder permissions. Easy enough to check:

1. Check the properties of a recent bak or trn file, security, and find out who the owner is.
2. Now check the properties of the backup FOLDER, security and see if the FILE owner from step 1 has enough effective permissions to delete files. The account might only have enough to create and modify, but not to remove files.

The peculiar part is that the plan always ran "successfully", even though it failed miserably. This is why teachers shouldn't let students grade their own tests. (grin).

---

What account is this running under? Domain Admin, service, etc?

I've always found it easier to create a batch job and use windows scheduler to clean up .bak files over x number of weeks. Can you look at the job history and see if the task failed / succeed, may be worth looking at the event viewer on the server as well.

---

The only solution I could find was to take the SQL that was generated from the cleanup task, and run that in an SP instead, 'cause guess what??? The SQL that this plan generates, runs perfectly!!!!!

This is the code I am using to run and it works.

ALTER PROCEDURE spUtility_delete_OldBackups
AS

DECLARE @date varchar(28)
SET @date = CONVERT(varchar(28),DATEADD(DAY, -5,GETDATE()))


EXECUTE master.dbo.xp_delete_file 0, 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\EEIDW\', 'bak', @date,1

EXECUTE master.dbo.xp_delete_file 0,'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\EEIDW\','diff', @date,1

EXECUTE master.dbo.xp_delete_file 0,'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\EEIDW\','trn', @date,1

---

I have a similar job that runs with no problems, what account does SQLServerAgent run under, these maintenance plans execute as SQLServerAgent, if the security context that SQLServerAgent runs under does not have adequate permissions, this job will fail, there should be some job history that will tell you more though.