0

This has been driving me crazy. I have tried all 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.

enter image description here

richard
  • 12,263
  • 23
  • 95
  • 151
  • Why are there close votes on this? Don't just vote to close without an explanation. – richard Apr 15 '11 at 01:58
  • can you show us the full file path from the plan, and compare that to the file path the backups are going to? Also, make sure that the task isn't disabled in the plan and make sure that it's connected to the backup task. – DForck42 Apr 15 '11 at 16:31

4 Answers4

2

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).

David Fowler
  • 21
  • 2
1

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.

JonH
  • 32,732
  • 12
  • 87
  • 145
1

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
richard
  • 12,263
  • 23
  • 95
  • 151
  • Well that has to indicate that it is a permissions issue doesn't it? – Martin Smith Apr 14 '11 at 20:03
  • @Martin: Any clues as to how to fix the permissions issue? – richard Apr 14 '11 at 20:59
  • Not off the top of my head. If you can reproduce this issue on a dev machine I'd probably try running Profiler to check that the `master.dbo.xp_delete_file` procedures were being called and then `procmon` to see any access denied errors on the file system (and what account was being denied access). – Martin Smith Apr 14 '11 at 21:18
0

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.

Ta01
  • 31,040
  • 13
  • 70
  • 99