title: Simultaneous SQL BULK INSERTs generate failures when using ErrorFile option
body: <p>Windows Server 2008 R2 Enterprise, SQL Server 2008 X64, SP3, Developer edition</p>
  
  <p>I build and dynamically execute (via sp_executesql) a BULK INSERT command. The general form is:</p>
  
  <pre><code>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'
   )
  </code></pre>
  
  <p>(The errorfile name is based on a configured local folder, the table being loaded, and a guid generated freshly for every bulk insert run -- it's a subroutine wrapped in its own stored procedure.)</p>
  
  <p>An outside process (was SQL Agent, is now a WCF service) launches DTEXEC which starts an SSIS package which calls stored procedures in a database that loop through sets, builds the query, and runs it for each. Up to four loads could be running at the same time from/into a given database, and multiple databases on the SQL instance could be running this at the same time – though historically, volume has been low, and we’ve generally only had one instance running this at a time. We do this a lot, and it has worked all but flawlessly for well over two years – security is properly configured, necessary files and folders exist, all the usual. (Luck? I like to think not.)</p>
  
  <p>We are now anticipating some serious workloads, so we’re doing some stress testing, in which I launch 8 runs, each with four processes, where a set of four will divide and one by one process the files to be loaded (i.e. up to 32 simultaneous bulk inserts being performed. Like I said, stress testing.) Low and behold, when launched, one or more will fail during the course of execution, with an error message like:</p>
  
  <p><code>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.).</code></p>
  
  <p>From run to run, the error does not occur for the same file, data set, or point-in-overall-processing.</p>
  
  <p>On the surface, it sounds like two processes are trying to access the same error file, which would mean that they’re independantly generating the same guid(!). My understanding is that’s supposed to be all but impossible. An alternate theory is, so much is going on simultaneously (potentially up to 32 simultaneous BULK INSERT commands running), SQL and/or the OS is getting confused somehow (I’m a DBA, not a network admin). I could do a work-around, building out my try-catch block to check for error 4861 and retrying up to three times, but I’d rather avoid such kludgery. </p>
  
  <p>I have since tossed in a routine that logs the name of the error file (with the guid) to a table before it is used. After many runs and several fails, I see that (a) the failed file + guid is being logged in my table, and (b) there are no duplicate guids being logged.</p>
  
  <p>Anyone know what might be going on?</p>
  
  <p>Philip</p>