xp_delete_file silently skips files if there are NTFS permission issues

xp_delete_file silently skips files if there are NTFS permission issues by Arvind Shyamsundar

Closed

as Won't Fix Help for as Won't Fix

Type:	Bug
ID:	496419
Opened:	10/7/2009 9:14:02 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

Maintenance plans have a cleanup task, which internally calls the xp_delete_file command. One can generate a T-SQL script from the SSMS GUI for maintenance plans which itself gives the syntax for xp_delete_file.

Many times, the cleanup task fails to cleanup stuff. In the maintenance plan logs all we see is a message that the task took hh:mm:ss seconds. No verbosity is provided, causing all kinds of problems when we troubleshoot cleanup task failures.

The purpose of this bug is to provide improved diagnostic messages for the command (could be exposed under retail trace flag) so that troubleshooting becomes easier.

Though the below repro is convoluted, the reality is that for troubleshooting purposes, the xp_delete_file command should give some errors in case of files which cannot be accessed - either because of permission issues or due to other issues (files locked by another process such as antivirus etc.) The error messages would aid in troubleshooting of problems such as maintenance plan cleanup task not actually cleaning up stuff.

Details (expand)

English

SQL Server 2008 SP1

SQL Engine

Windows Server 2008

US English

1. Create a transaction log backup for any database in full recovery model. Place it in the c:\temp folder (any other folder should work)

backup log test to disk = 'c:\temp\test.trn' with init

1.1 Using Windows Explorer, explicity edit the NTFS permissions for the file and deny the SQL Service account 'Full Control' permissions on this file.

2. Execute the following command (you may change the date accordingly)

xp_delete_file 0, 'c:\temp', 'trn', '2009-11-01'

The c:\temp\TEST.TRN file file does not get deleted.

Error messages (ACCESS_DENIED etc.) from the xp_delete_file command stating the reasons for skipping the file.

0 attachments

Posted by Timothy Ford on 6/5/2012 at 9:35 AM

This xp is used by SQL Server to perform file cleanup tasks in Maintenance Plans so I am quite interested to know why it's being ignored and listed as Won't Fix as an undocumented feature. I think the SQL MVPs and the community at large need some clarification as to why this is considered undocumented, yet it is the core process for backup file cleanup.

Thank you.

Tim Ford, SQL Server MVP

Posted by Microsoft on 5/21/2011 at 11:08 PM

Hi:

We took a look at this bug along with several others recently. Unfortunately, triaging it against other critical bugs, I do not think we would get to investigating this in the near future. However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this resolved.

Thanks for writing in to Microsoft.

Cheers,

Chandramouli | Program Manager | SQL Server Manageability

Posted by Microsoft on 5/11/2010 at 4:23 AM

Hi Arvindsh,

Thanks for your feedback. We will consider your request of logging the more diagnostic information when cleanup task got failed for a future release of SQL Server.

Thanks,
Sateesh Pragallapati

Posted by pnauta on 4/8/2010 at 3:39 AM

@Steve Kass: Three others have already commented that it should be documented in the first place. This makes your argument invalid for thowing this one out.

The dive deeper into this: if you throw out all undocumented features, you end up with a bare SQL engine, and as maintenance possibilities are part and parcel of the sales pitch, it should be supported and documented.

De driver behind this is of course that this XP is full of bugs in SQL 2005. As soon as it's documented, one knows what can be expected behaviour.

Posted by Steve Kass on 10/10/2009 at 7:03 PM

xp_delete_file is an undocumented stored procedure. If there is a problem with some documented SQL Server 2008 feature that happens to use xp_delete_file internally, I suggest you post a bug report that is more obviously about that feature. Your bug report names an undocumented feature in the bug title, so it's not likely to be taken seriously.