SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_delete_file not working in version SQL Server 2008 R2 Standard Edition.


xp_delete_file not working in version SQL Server 2008 R2 Standard Edition.

Author
Message
ss-457805
ss-457805
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 2126
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.

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250944 Visits: 9673
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?
ss-457805
ss-457805
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 2126
I don't see any error message in windows or sql logs. It says command completed successfully.

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250944 Visits: 9673
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.
ss-457805
ss-457805
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 2126
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.

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250944 Visits: 9673
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.
ss-457805
ss-457805
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 2126
found the issue. The customer gave sql server service account public role. Changed that to sysadmin. it's working fine now.

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250944 Visits: 9673
So the account had write permissions but not delete?


How come that didn't trigger any errors anywhere?
ss-457805
ss-457805
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5071 Visits: 2126
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?

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)SSC Guru (250K reputation)

Group: General Forum Members
Points: 250944 Visits: 9673
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search