sql-server - 使用ErrorFile选项时，同时SQL BULK INSERT生成失败

Question

Windows Server 2008 R2 Enterprise，SQL Server 2008 X64，SP3，开发人员版

我构建并动态执行（通过sp_executesql）一个BULK INSERT命令。通用形式为：

BULK INSERT #HeaderRowCheck
 from "\\Server\Share\Develop\PKelley\StressTesting\101\DataSet.csv"
 with
 (
   lastrow = 1
  ,rowterminator = '\n'
  ,tablock
  ,maxerrors = 0
  ,errorfile = 'C:\SQL_Packages\TempFiles\#HeaderRowCheck_257626FB-A5CD-41B8-B862-FAF8C591C7A9.log'
 )

（错误文件名基于配置的本地文件夹，正在加载的表以及每次批量插入运行都会重新生成的guid-它是包装在其自己的存储过程中的子例程。）

外部进程（以前是SQL Agent，现在是WCF服务）启动DTEXEC，DTEXEC启动SSIS程序包，该程序调用数据库中的存储过程，该过程遍历集合，构建查询并为每个查询运行。最多可以同时从一个给定的数据库运行一个负载，或同时运行一个给定的数据库，并且SQL实例上的多个数据库可以同时运行–尽管从历史上看，数量很少，而且通常只有一个实例一次运行。我们已经做了很多工作，并且在过去两年中，它几乎完美地工作了–安全性得到了正确配置，存在必要的文件和文件夹，而且通常都是如此。 （运气？我想不。）

现在，我们预计会出现一些严重的工作负载，因此我们正在进行一些压力测试，其中我启动了8个运行，每个运行有四个进程，其中四个将分开，一个进程一个接一个地加载文件（即最多同时执行32个批量插入操作。就像我说过的那样，进行了压力测试。）低速运行时，启动时一个或多个将失败，并显示以下错误消息：

Error #4861 encountered while loading header information from file "DataSet.csv": Cannot bulk load because the file "C:\SQL_Packages\TempFiles\#HeaderRowCheck_D0070742-76A5-4175-A1A7-16494103EF25.log" could not be opened. Operating system error code 80(The file exists.).

从运行到运行，同一文件，数据集或整体处理点均不会发生该错误。

从表面上看，听起来好像两个进程正在尝试访问相同的错误文件，这意味着它们正在独立生成相同的guid（！）。据我了解，这几乎是不可能的。另一种理论是，很多事情同时发生（可能同时运行32个BULK INSERT命令），SQL和/或操作系统因某种原因而变得混乱（我是DBA，而不是网络管理员）。我可以采取一种变通方法，建立我的try-catch块来检查错误4861，然后最多重试3次，但是我宁愿避免这种麻烦。

从那以后，我就陷入了一个例程，该例程在使用错误日志之前将错误文件的名称（带有guid）记录到一个表中。在多次运行并且几次失败之后，我看到（a）失败的文件+ guid正在记录在我的表中，并且（b）没有重复的guid被记录。

有人知道会发生什么吗？

菲利普

Answer 1

我与Microsoft技术支持一起开了一个案子，经过反复的反复，Pradeep M.M. （SQL Server支持技术负责人）全力以赴。

一般过程：读取文件夹中的文件列表，然后一个个地对这些文件执行一系列批量插入操作（首先读取第一行，我们将其解析为列，然后读取第二行以上的数据） ）。所有批量插入均使用“ ErrorFile”选项，以便为用户提供错误格式的数据时我们可以提供的信息。流程已经运行了3年以上，但是在最近的压力测试条件下（单个SQL Server实例最多执行8个同时运行，并且所有文件的格式正确），我们得到了上面列出的错误。

最初，尽管由于产生了“已经打开”的错误而导致生成GUID时出现了错误，但是最终还是放弃了这个想法-如果newid（）无法正常运行，那么将会有更多的人遇到更严重的问题。

根据Pradeep，这是批量插入工作原理的分步过程：


批量插入命令已提交并已分析语法错误
然后编译BULK INSERT命令以生成执行
计划相同
在编译阶段（如果在查询中）是否已指定
ERRORFILE参数，然后我们将创建ErrorFile.log和
ErrorFile.Error.Txt到指定的文件夹位置（重要
这里要了解的是文件大小为0kb）
文件创建完成后，我们将使用删除这两个文件
Windows API调用
一旦执行计划准备就绪，我们进入执行阶段
并尝试执行“批量插入”命令作为其中的一部分
重新创建ErrorFile.log和ErrorFile.Error.Txt到文件夹
指定的位置（根据联机丛书文档中的错误
文件不应放在此位置，否则我们将无法通过
执行http://msdn.microsoft.com/en-us/library/ms188365.aspx
执行完毕后，如果
批量插入各自的错误记录到错误文件中
如果没有错误创建，则这2个文件将被删除。


在失败的运行过程中运行ProcMon（进程监视器）显示ErrorFile在步骤3中成功创建并打开，但在步骤4中未关闭，导致步骤5生成了我们看到的错误。 （为成功运行，已按预期创建并关闭了文件。）

对ProcMon的进一步分析表明，尝试批量插入后，另一个运行CMD.EXE的进程正在对文件发出“关闭句柄”操作。我们使用一个涉及xp_cmdshell的例程来检索要处理的文件列表，这将是CMD.EXE进程的原因。这是踢球者：

…有一些业务逻辑会在SQL Server内部启动CMD.EXE，并且由于CMD.EXE是子进程，因此它继承了由父进程打开的所有句柄（因此，这可能是某种时序问题，其中CMD.EXE中包含句柄对于在启动时打开并且所有句柄都被CMD.EXE继承的文件都是无法删除的，只能在CMD.EXE被销毁后才能释放）

就是这样。单次运行永远不会遇到此问题，因为在发出大容量插入之前，它的xp_cmdshell调用已完成。但是在并行运行中，尤其是在许多并行运行中（我只遇到了5次或更多运行的问题），发生了计时问题，使得：


SSIS包之一执行并调用存储过程
内部使用XP_CMDSHELL并启动CMD.EXE进行枚举
文件
与SQL Server的相同连接完成文件枚举
然后启动批量插入活动，它处于编译状态
BULK INSERT命令的阶段
根据批量插入的设计，我们确实创建了ErrorFile
在编译阶段，然后在编译后将其删除
阶段完成
同时，另一个SSIS包被执行并调用
内部使用XP_CMDSHELL并启动的存储过程
CMD.EXE枚举所有文件
CMD.EXE是在父项下启动的子进程
处理SQLServr.exe，因此默认情况下它将继承所有句柄
由SQLServr.exe创建（因此，此过程将获取
首先由大容量插入创建的ERRORFILE
连接）
现在在“首次连接”中，编译阶段结束，
因此，我们正在尝试删除必须关闭的文件
所有句柄，我们确实看到CMD.EXE持有该句柄
文件，它仍然处于打开状态，因此我们无法删除该文件。所以
在不删除文件的情况下，我们进入执行阶段
在执行阶段，我们正在尝试使用
相同的名称，但是由于文件已经存在，因此我们将失败并显示错误
“操作系统错误代码80（该文件存在。）。”


我的短期变通办法是（1）实现重试循环，生成新的ErrorFile名称，并在放弃之前尝试最多进行三次新的大容量插入，以及（2）在我们的夜间进程中构建另一个例程以删除找到的所有文件在我们的“ ErrorFile文件夹”中。

长期修复是修改我们的代码，使其不通过xp_cmdshell列出文件。这似乎是可行的，因为整个ETL过程都由SSIS程序包包装和管理。或者，可以构建和使用CLR例程。就目前而言，鉴于我们预期的工作量，解决方法已足够（尤其是鉴于我们目前正在处理的所有其他事情），因此在执行最终定稿之前可能要花点时间固定。

为后代发贴，以防万一！