xp_deletefile参数如何使用？

xp_delete_file 是一个 SQL Server 扩展存储过程，它位于 msdb 数据库中，它的主要功能是清理 SQL Server 代理作业生成的旧文件，例如备份文件、报表快照文件等，这比手动编写 xp_cmdshell 调用 del 命令要更安全、更可控。

基本语法

USE msdb;
GO
EXEC xp_delete_file
    { [ @file_type = ] 'type' }
    , [ @file_name = ] 'file_name'
    , [ @file_extension = ] 'extension'
    , [ @directory = ] 'directory'
    , [ @older_than = ] 'date'
    , [ @result = ] result OUTPUT

参数详解

下面我们来逐一分析每个参数。

@file_type = 'type' (必填)

这个参数指定了要删除的文件类型，SQL Server 内置了对几种特定文件类型的识别，它不仅仅根据文件扩展名,还会根据文件名中的特定模式。

重要提示: 即使你指定了 @file_extension，@file_type 的值也会优先影响文件的选择逻辑。@file_type = 'B' 会寻找所有备份文件，而不仅仅是 .bak 文件。

@file_name = 'file_name' (可选)

这个参数用于指定一个特定的文件名，如果你只想删除一个特定的文件，而不是整个目录下所有符合条件的文件,就使用这个参数。

• 如果提供此参数：@directory 和 @older_than 参数将被忽略,系统会直接尝试删除指定路径下的这个文件。
• 如果省略此参数：xp_delete_file 会根据 @file_type、@file_extension 和 @older_than 条件来删除目录下所有匹配的文件。

@file_extension = 'extension' (可选)

指定文件的扩展名，.bak, .trn, .out。

• 如果省略此参数，则会使用 @file_type 对应的默认扩展名。
• 强烈建议始终提供这个参数，以避免误删。@file_type = 'B' 可能会匹配到 .txt 文件（如果文件名中包含 SQL），如果你不指定 @file_extension = '.bak',可能会有风险。

@directory = 'directory' (可选)

指定要搜索和删除文件的完整目录路径。

• 如果省略此参数，则默认使用 SQL Server 代理作业的默认输出目录，这个目录通常是 C:\Program Files\Microsoft SQL Server\MSSQL<version>\MSSQL\JOBS。
• 最佳实践：始终明确指定一个完整的目录路径，避免依赖默认值，因为默认值在不同 SQL Server 版本或安装配置下可能不同。

@older_than = 'date' (可选)

这是一个非常重要的参数，用于指定一个截止日期，只有修改时间早于这个日期的文件才会被删除。

• 必须使用有效的 SQL Server 日期格式，'YYYYMMDD' 或 'YYYY-MM-DD HH:MI:SS'。
• 如果省略此参数，不会删除任何文件,这是防止意外删除所有文件的一个安全机制。

@result = result OUTPUT (可选)

这是一个输出参数,用于存储操作影响的文件数量。

• 它返回一个整数,表示成功删除的文件总数。
• 这个参数对于验证操作是否按预期执行非常有用,例如在脚本中可以检查返回值是否大于0。

使用示例

示例 1：删除 7 天前的所有 .bak 备份文件

假设你的备份文件都存放在 D:\SQL_Backups 目录下。

USE msdb;
GO
DECLARE @deleted_files INT;
EXEC xp_delete_file
    @file_type = 'B',          -- 指定备份文件类型
    @file_extension = '.bak',  -- 明确指定扩展名
    @directory = 'D:\SQL_Backups', -- 指定目录
    @older_than = DATEADD(DAY, -7, GETDATE()), -- 删除7天前的文件
    @result = @deleted_files OUTPUT; -- 输出删除的文件数量
PRINT '成功删除了 ' + CAST(@deleted_files AS VARCHAR(10)) + ' 个文件。';
GO

示例 2：删除一个特定的代理作业输出文件

假设要删除 D:\SQL_Job_Output\MySpecialJob.out 这个文件。

USE msdb;
GO
EXEC xp_delete_file
    @file_type = 'D',           -- 代理作业文件类型
    @file_name = 'MySpecialJob.out', -- 指定具体文件名
    @directory = 'D:\SQL_Job_Output'; -- 指定目录
GO

注意：在这个例子中，@older_than 参数被忽略了，因为 @file_name 被提供了。

示例 3：删除 30 天前的所有 .trn 日志备份文件

USE msdb;
GO
EXEC xp_delete_file
    @file_type = 'B', -- 备份文件类型也适用于日志备份
    @file_extension = '.trn',
    @directory = 'D:\SQL_Backups',
    @older_than = DATEADD(DAY, -30, GETDATE());
GO

重要注意事项

1. 权限要求：执行 xp_delete_file 的账户需要拥有对目标目录的写入和删除权限，运行 SQL Server 服务的账户 (NT SERVICE\MSSQLSERVER 或类似账户) 需要有这些权限。
2. 安全性：xp_delete_file 比 xp_cmdshell 更安全，因为它只能在 SQL Server 内部执行，并且有严格的参数验证,但仍然建议不要授予普通用户执行此存储过程的权限。
3. 谨慎使用：在执行删除操作前，务必检查 @directory 和 @older_than 参数是否正确,建议先在非生产环境或业务低峰期进行测试。
4. 默认目录：避免依赖 @directory 的默认值,最好总是显式指定。
5. 日志记录：对于重要的清理任务，建议将 @result 输出值记录到日志表中,以便追踪和审计。

通过理解这些参数，你就可以安全、有效地使用 xp_delete_file 来自动化管理 SQL Server 产生的各种文件。