SQL Server 2017使用sys.dm_os_enumerate_filesystem查询大目录报错求助

问题背景 #

使用SQL Server 2017 (RTM-CU31) 版本：

Microsoft SQL Server 2017 (RTM-CU31) (KB5016884) - 14.0.3456.2 (X64) Sep 2 2022 11:01:50 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2012 Standard 6.2  (Build 9200: ) (Hypervisor)

执行SELECT * from sys.dm_os_enumerate_filesystem('F:\technician','*');查询460GB的大目录时，触发错误：

Msg 407, Level 16, State 1, Line 15 internal error. The string routine in file sql\ntdbms\storeng\dfs\alloc\storagedmv.cpp, line 799 failed with HRESULT 0x8007007a

已确认SQL Server服务账号拥有目录完全权限，且已安装最新CU补丁。

错误分析 #

HRESULT 0x8007007a对应Windows系统错误ERROR_INSUFFICIENT_BUFFER（缓冲区不足），说明sys.dm_os_enumerate_filesystem内部用于存储文件路径、名称的缓冲区无法容纳大目录下的海量文件/子目录信息，导致字符串处理逻辑崩溃。

解决方案建议 #

• 分批拆分查询：避免一次性枚举整个大目录，通过通配符或层级拆分缩小查询范围：
  • 先枚举子目录：SELECT * from sys.dm_os_enumerate_filesystem('F:\technician','*') WHERE is_directory = 1;，再逐个查询子目录内容
  • 按文件名前缀分批查询：SELECT * from sys.dm_os_enumerate_filesystem('F:\technician','a*');、SELECT * from sys.dm_os_enumerate_filesystem('F:\technician','b*');等
• 使用替代工具/方法：
  • 启用xp_cmdshell（需注意安全配置）执行dir命令获取文件列表，再解析结果：

    EXEC xp_cmdshell 'dir "F:\technician" /s /b';
    

  • 编写CLR存储过程实现自定义文件枚举逻辑，可灵活控制缓冲区大小和枚举规则
  • 用PowerShell脚本枚举目录后将结果导入SQL Server：

    Get-ChildItem -Path "F:\technician" -Recurse | Select-Object FullName, Name, Length, LastWriteTime | Export-Csv -Path "C:\temp\files.csv" -NoTypeInformation
    

    再通过BULK INSERT导入数据到SQL表
• 排查异常文件：检查目录中是否存在路径长度超过260字符的文件（Windows传统路径限制），或包含特殊字符的文件名，这类文件可能导致DMV的字符串处理逻辑出错，可先临时移走这类文件测试
• 提交微软反馈：由于已安装最新CU补丁仍出现问题，该问题可能属于未修复的产品bug，可通过微软官方支持渠道提交错误报告，附带完整错误信息、环境配置和目录结构信息

内容的提问来源于stack exchange，提问作者msalese