September 28, 2010 at 8:45 am
Dear Friends
I've got an issue at one of the customer site where the old backups are not getting deleted. I used clean up task in maintenance plan. That didn't work. ran the below
script. That didn't work either. I have taken the date field off just to see if there was an issue with the date maybe..It doesn't seem to be the case. It's just not working.
EXECUTE master.dbo.xp_delete_file 0,N'G:\SQLBackups\SERVER3$ECLIPSE\ECLIPSE4\FULLBU\',N'bak'
I am using:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
Many thanks in advance.
September 28, 2010 at 8:55 am
Are you sure the path is ok for the delete?
There's nothing else obvious other than not having permission to run that xSP.
Do you get any errors when running it manually?
What messages do you have in sql and windows logs?
September 28, 2010 at 9:06 am
I don't see any error message in windows or sql logs. It says command completed successfully.
September 28, 2010 at 9:19 am
I'd scale back to pinpoint the error.
Put a test file on G:\ and try deleting that single file.
Then I'd try the same thing using the extension.
Once you're 100% sure your syntaxe is right you can move on :
I'd then move down to the first dir where there's $ sign and try a delete there.
That $ sign might need to be escaped somehow.
Let me know how that goes for you.
September 28, 2010 at 9:38 am
I've put the file in G drive and ran the below script:
EXECUTE master.dbo.xp_delete_file 0,N'G:\',N'bak'
I don't get any error message. It says it completed successfully, but my file is still there.;-( So it can't be the $ sign.
September 28, 2010 at 9:43 am
I'd personnally try with xp_cmdshell (good old dos commands) and try the same steps as above.
I'd also ensure that I'm working on the correct server.
September 29, 2010 at 2:38 am
found the issue. The customer gave sql server service account public role. Changed that to sysadmin. it's working fine now.
September 29, 2010 at 4:36 am
So the account had write permissions but not delete?
How come that didn't trigger any errors anywhere?
September 29, 2010 at 7:07 am
It's backing up the database fine. but not deleting it. so eventhough i gave full control of the folder to the SQL Server service account it wasnt deleting the old backups. But when the sysadmin role was given to this account it's deleting the old backups successfully. i wasn't seeing any error messages anywhere whther it's windows logs or sql logs. Y is this?
September 29, 2010 at 7:30 am
ss-457805 (9/29/2010)
It's backing up the database fine. but not deleting it. so eventhough i gave full control of the folder to the SQL Server service account it wasnt deleting the old backups. But when the sysadmin role was given to this account it's deleting the old backups successfully. i wasn't seeing any error messages anywhere whther it's windows logs or sql logs. Y is this?
No idea. As far as I can remember I always got a error like permission denied when I couldn't do something on the drive. Maybe it's a bug with sql 2008 or your windows' version.
September 29, 2010 at 7:58 am
do you think it's a bug with SQL Server 2008 R2?
September 29, 2010 at 8:34 am
Could be, I'd start a thread on connect to see what's really going on.
September 30, 2010 at 9:33 am
I had bad experience to delete backup files using the extended sproc. Seemed that this sproc can only delete the backup files sql created. It can not delete backup files created by litespeed.
September 22, 2017 at 8:21 am
I know this is an old post but I had the same issue today (09/22/2017) so I'm posting this in case it is of value to others.
ISSUE: Maintenance plan was not deleting the old log files (.txt) from the full backup job. However, no problems deleting the old backup files (.bak).
RESOLUTION: Add full control permissions for the MSSQLSERVER service account to the folder where the log files are written.
My environment (new physical server, all software installed about 2 months ago):
I had manually created a folder to store the log files (.txt files) for backup job status logging but let SQL Server create the subfolder(s) for the full backups (.bak files) upon the first execution of the maintenance plan. Naturally, SQL Server added the appropriate permissions to the folder it created so that deleting old backups (.bak files) from within the maintenance plan worked fine. However, the logs (.txt files) for reporting the status of the backups in the manually created "Log" folder were not being deleted. I manually executed the xp_delete_file command from a query editor window in SSMS and it reported that the "Command(s) completed successfully." when, in fact, the log files were not being deleted. No error messages or indications of inaccessibility or permissions issues were reported. Upon reviewing the permissions on the Log folder (manually created by me) and comparing it to the permissions on the full backup folder (created by SQL Server), I could see that the full backup folder contained an entry for the SQL Server (MSSQLSERVER) service account with full control. So I added the same permission to the manually created Log folder and that resolved the issue. To be clear, I did not add explicit permissions for the SQL Server Agent service account ... I only added explicit permissions for the SQL Server service account.
Hope this is helpful to someone.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply