September 9, 2012 at 4:01 pm
I have a puzzle concerning the cleanup portion of a SQL Server 2012 maintenance plan created through the design interface of the wizard. The full and incremental backups to a file share on the SAN are running fine and the cleanup is targeting exactly the same URI, however we started getting disk space alarms since none of the expired backups were actually being removed. The full, differential and transaction log backups are all set to expire after three days. I went into the plan and it says this is the approximate T-SQL being run to delete expired backup items older than four days:
EXECUTE master.dbo.xp_delete_file 0,N'\\theFileServer\theBackupDirectory',N'bak',N'2012-09-05T14:22:07',1
That looked fine to me and I ran it in a query window to test with the result 'Command(s) completed successfully.' but they actually did not! Further if you look at the agent log it says the plan segment did finish, likewise querying sysjobhistory directly.
The bottom line is no files were deleted and I resorted to running a PowerShell script to delete the expired files. Does anyone have a suggestion?
September 9, 2012 at 5:22 pm
Did you use a 3rd party tool to create the backups that need to be cleaned up?
If so, that could be the problem since that maintenance cleanup task (in my experience) cannot delete the files unless they were generated by SQL Server.
September 9, 2012 at 5:31 pm
Verify the security settings on the share - if the service account does not have full permissions on the folder it won't be able to delete the files.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 10, 2012 at 8:29 pm
No, the SQL Server 2012 Maintenance Plan Wizard.
September 10, 2012 at 8:38 pm
I think you are right, the engineer who created the backup directory gave 'everyone' write and modify permission, and the backup was running under the the network service account which had no direct permissions. I just changed it to an account we use for SQL Server that has full control on the backup folder, so I'll see tomorrow whether the backups are being deleted via the maintenance plan.
Still puzzled though, as to why SQL Server was reporting the file delete as a 'successful' operation.
September 10, 2012 at 8:40 pm
Let us know how it goes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply