SQL Server xp_delete_file 不删除文件

SQL Server xp_delete_file not deleting files

提问by Chris Burgess

I'm trying to write some SQL that will delete files of type '.7z' that are older than 7 days.

我正在尝试编写一些 SQL 来删除超过 7 天的“.7z”类型的文件。

Here's what I've got that's not working:

这是我无法正常工作的内容：

DECLARE @DateString CHAR(8)
SET @DateString = CONVERT(CHAR(8), DATEADD(d, -7, GETDATE()), 1)
EXECUTE master.dbo.xp_delete_file 0, 
                  N'e:\Database Backups',N'7z', @DateString, 1

I've also tried changing the '1' a the end to a '0'.

我也尝试将“1”的结尾更改为“0”。

This returns 'success', but the files aren't getting deleted.

这将返回“成功”，但文件不会被删除。

I'm using SQL Server 2005, Standard, w/SP2

我使用的是 SQL Server 2005，标准版，带 SP2

回答by

Had a similar problem, found various answers. Here's what I found.

有类似的问题，找到了各种答案。这是我发现的。

You can't delete 7z files with xp_delete_file. This is an undocumented extended stored procedure that's a holdover from SQL 2000. It checks the first line of the file to be deleted to verify that it is either a SQL backup file or a SQL report file. It doesn't check based on the file extension. From what I gather its intended use is in maintenance plans to cleanup old backups and plan reports.

您无法使用 xp_delete_file 删除 7z 文件。这是一个未记录的扩展存储过程，它是 SQL 2000 的保留过程。它检查要删除的文件的第一行，以验证它是 SQL 备份文件还是 SQL 报告文件。它不会根据文件扩展名进行检查。据我所知，它的预期用途是用于清理旧备份和计划报告的维护计划。

Here's a sample based on Tomalak's link to delete backup files older than 7 days. What trips people up is the 'sys' schema, the trailing slash in the folder path, and no dot in the file extension to look for. The user that SQL Server runs as also needs to have delete permissions on the folder.

这是一个基于 Tomalak 链接的示例，用于删除超过 7 天的备份文件。让人们感到不安的是“sys”模式、文件夹路径中的尾部斜杠，以及文件扩展名中没有要查找的点。运行 SQL Server 的用户也需要对该文件夹具有删除权限。

DECLARE @DeleteDate datetime
SET @DeleteDate = DateAdd(day, -7, GetDate())

EXECUTE master.sys.xp_delete_file
0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
N'D:\SQLbackups\', -- folder path (trailing slash)
N'bak', -- file extension which needs to be deleted (no dot)
@DeleteDate, -- date prior which to delete
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

Note that xp_delete_file is broken in SP2 and won't work on report files; there's a hotfix for it at [http://support.microsoft.com/kb/938085]. I have not tested it with SP3.

请注意，xp_delete_file 在 SP2 中已损坏，无法处理报告文件；在 [ http://support.microsoft.com/kb/938085] 上有一个修补程序。我还没有用 SP3 测试过它。

Since it's undocumented, xp_delete_file may go away or change in future versions of SQL Server. Many sites recommend a shell script to do the deletions instead.

由于它没有记录，因此 xp_delete_file 可能会在 SQL Server 的未来版本中消失或更改。许多站点推荐使用 shell 脚本来代替执行删除操作。

回答by Jorge Ferreira

AFAIK xp_delete_fileonly delete files recognized by SQL Server 2005 (backup files, transaction logs, ...). Perhaps you can try something like this:

AFAIKxp_delete_file仅删除 SQL Server 2005 识别的文件（备份文件、事务日志等）。也许你可以尝试这样的事情：

xp_cmdshell 'del <filename>'

回答by Eduardo Molteni

This sp will only delete native sql server backup files or native maintenance report files (for security purposes)

这个sp只会删除本机sql server备份文件或本机维护报告文件（出于安全考虑）

As Smink suggested you can use

正如 Smink 建议您可以使用

xp_cmdshell 'del <filename>'

With the proper permissions on the folder.

对文件夹具有适当的权限。

回答by Holger

I found this question, but the solution didn't apply to me (as it was .bak files, SQL Server itself had made, as part of a Maintenance Plan).

我发现了这个问题，但该解决方案不适用于我（因为它是 .bak 文件，SQL Server 自己制作，作为维护计划的一部分）。

The issue in my case was security. The script was being run as the user which starts SQL Server (MSSQL) (in my case and probably most cases "network service") didn't have access to the folder it was trying to delete files in.

我的问题是安全性。该脚本作为启动 SQL Server (MSSQL)（在我的情况下，可能在大多数情况下为“网络服务”）的用户无法访问它试图删除文件的文件夹而运行。

So adding "network service" and granting it "modify" helped.

所以添加“网络服务”并授予它“修改”有帮助。

回答by user5923365

I had read many different approaches and solutions multiple individuals pursued when attempting to resolve the issue with the extended stored procedure xp_delete. The solutions are:

在尝试使用扩展存储过程 xp_delete 解决问题时，我已经阅读了许多不同的方法和解决方案。解决方法是：

1. Be sure to NOT have a period (.) in the extension when configuring the SSIS maintenance task.
2. Be sure to click on the Include First-Level sub folders if they exist for each database backup.
3. Be sure to click on the backup files at the top. The maintenance task does check the file type. For database backups, I believe it checks the backup file header.

1. 配置 SSIS 维护任务时，请确保扩展名中没有句点 (.)。
2. 如果每个数据库备份都存在，请确保单击“包含一级子文件夹”。
3. 请务必单击顶部的备份文件。维护任务会检查文件类型。对于数据库备份，我相信它会检查备份文件头。

In my scenario, all of the above were correct. There are few comments on the web where some of said the routine xp_delete is buggy.

在我的场景中，以上所有内容都是正确的。网络上很少有评论说例程 xp_delete 有问题。

When the backup files were not being deleted, I extracted the SQL for the maintenance and ran it from SSMS. The resulting message was the file was not a sql server backup file. This message was erroneous as the backup could be restored successfully, resulting in an operational database.

当备份文件没有被删除时，我提取了用于维护的 SQL 并从 SSMS 运行它。结果消息是该文件不是 sql server 备份文件。此消息是错误的，因为备份可以成功恢复，从而导致数据库正常运行。

The database commands used to verify the database were:

用于验证数据库的数据库命令是：

RESTORE HEADERONLY FROM DISK = N'<file path\filename>.Bak'
RESTORE VERIFYONLY FROM DISK = N'<file path\filename>.bak'

Both of the above commands indicated the backup file was valid.

以上两个命令都表明备份文件有效。

Next I opened the event viewer and found messages indicating there were login errors for the connection manager. This was strange because I had validated the connection with the test connection button. The errors were not related to any account I had created.

接下来，我打开事件查看器，发现消息表明连接管理器存在登录错误。这很奇怪，因为我已经使用测试连接按钮验证了连接。这些错误与我创建的任何帐户无关。

Event Viewer Message:

事件查看器消息：

*The description for Event ID 17052 from source MS SQL SERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer. If the event originated on another computer, the display information had to be saved with the event.

*无法找到来自源 MS SQL SERVER 的事件 ID 17052 的描述。您的本地计算机上未安装引发此事件的组件或安装已损坏。您可以在本地计算机上安装或修复该组件。如果事件起源于另一台计算机，则显示信息必须与事件一起保存。

The following information was included with the event:

活动中包含以下信息：

Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'domain\servername$'.*

严重性：16 错误：18456，操作系统：18456 [Microsoft][SQL Server Native Client 11.0][SQL Server]用户“域\服务器名称$”登录失败。*

Next I logged onto a machine where xp_delete was functioning correctly. After reviewing the active directory and not finding the system account, I proceeded to the event viewer to find similar messages. Here it became evident the account for domain\server$ is mapped to system security.

接下来我登录到一台 xp_delete 运行正常的机器。查看活动目录并没有找到系统帐户后，我继续使用事件查看器查找类似消息。这里很明显 domain\server$ 的帐户被映射到系统安全。

Next step was to compare the database security where xp_delete worked against the database where it did not work. There were 2 missing logins under security in the database where xp_delete did not work. The 2 missing logins were: NT AUTHORITY\SYSTEM NT Service\MSSQLSERVER

下一步是将 xp_delete 工作的数据库安全性与它不起作用的数据库进行比较。在 xp_delete 不起作用的数据库中，安全性下有 2 个丢失的登录名。2 个丢失的登录名是：NT AUTHORITY\SYSTEM NT Service\MSSQLSERVER

After adding NT service\MSSQLSERVER, xp_delete successfully worked.

添加NT service\MSSQLSERVER后，xp_delete成功运行。

One approach to testing is to use the maintenance task to delete an individual file.

一种测试方法是使用维护任务删除单个文件。

回答by dwjv

I know this is a little old but I wanted to share my frustrations with you all. I was having the same problem as a lot of these posts but nothing seemed to work. I then remembered that we have an encryption layer on the database called NetLib. This means that the backups are encrypted and as such, xp_delete_file cannot read the headers. I now use a batch file in the OS and call it from an agent job. Hope this helps someone.

我知道这有点旧，但我想与大家分享我的挫败感。我遇到了与许多这些帖子相同的问题，但似乎没有任何效果。然后我想起我们在名为 NetLib 的数据库上有一个加密层。这意味着备份已加密，因此 xp_delete_file 无法读取标头。我现在在操作系统中使用批处理文件并从代理作业调用它。希望这可以帮助某人。

回答by Jivomir Yovkov

We usually end up in such situations when you have the database moved to another server or when a SQL instance is reinstalled on the same one but the backup is left in the old directory. For example: You move the database from server1 to server2, but you have a server with a maintenance plan which performs a periodic backup or you reinstall the SQL instance on server1 and you restore the database.

当您将数据库移动到另一台服务器或在同一台服务器上重新安装 SQL 实例但备份保留在旧目录中时，我们通常会遇到这种情况。例如：您将数据库从 server1 移动到 server2，但您有一台带有维护计划的服务器，该服务器执行定期备份，或者您在 server1 上重新安装 SQL 实例并恢复数据库。

In the backup case the sets which are kept as information in msdb are no longer there, therefore all older backups which have been created will not be deleted as no information is checked from the fails derived from the tables with backup sets.

在备份情况下，作为信息保留在 msdb 中的集不再存在，因此不会删除所有已创建的旧备份，因为不会从具有备份集的表派生的失败中检查任何信息。

EXECUTE master.sys.xp_delete_file  0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)

The first argument shows that the tables from msdb are being used.

第一个参数表明正在使用来自 msdb 的表。

Hope this helps someone.

希望这可以帮助某人。

回答by Tomalak

Try changing the first parameter from 0 to 1.

尝试将第一个参数从 0 更改为 1。

Here is a small summary on xp_delete_fileI just found. Sounds a bit like you'd be out of luck with this procedure.

这是我刚刚找到的一个小总结xp_delete_file。听起来有点像你不走运这个程序。