We have a batch import process that BULK INSERTs raw data into staging tables, does some data mapping, and then pushes it to another database for validation.

The procedure where my issue occurs is very long and is filled with column names so I'll give you the run down:

  • Raw files are BULK INSERTED into various Import Tables. If this succeeds, those records begin migration to the validation database.

  • New GUIDs are generated for the Validation Database (@ValBatchId)

  • Basic Record information is put into a Records table

INSERT INTO [ValidationDatabase].[dbo].[RecordsTable]([RecordId],[BatchId],[Columns...]) ( SELECT NewId(), @ValBatchId, Columns... FROM [ImportRecordDataTable] )

  • Import Tables have their data mapped from provided values to standard values and are inserted into their respective tables in the Validation database

While performing the last step of the process, it will occasionally silently fail and insert 0 records into a specific table:

INSERT INTO [ValidationDatabase].[dbo].[Table]
        SELECT rec.RecordId, imp.stuff...
        FROM [ValidationDatabase].[dbo].[RecordsTable] as rec
        INNER JOIN [ImportDatabase].[dbo].[ImportTable] as imp ON...
    WHERE rec.BatchId = @ValBatchId AND imp.BatchId = @ImportBatchId

I added some diagnostic code in the stored procedure to makes sure this was a SQL Server error and not a disk issue by adding @@ROWCOUNT into a logging table:


      INSERT INTO [ImportDatabase].[dbo].[MigrateLog]([BatchID],[TransactionDate],[Description])
      VALUES(@ImportBatchId,GETDATE(), CAST(@RowsInserted AS VARCHAR(MAX)) + ' records inserted into <table name>') 

...where it displayed this for a particular batch:

inserting into <table name>

0 records inserted into <table name>

Here's the kicker: Immediately after the rest of the migration process finishes, if I go and rerun the select statement inside the insert...and it returns the data that should have been inserted. Most disturbingly, If I go and re-import that batch of data (as a completely separate batch, without any reference to the failed batch) it will work without issue.

I can't for the life of me figure out what is going on. It has to be the SELECT statement inside the INSERT that isn't returning records but I can't recreate this problem, even with the same batch it happens on. Is this a bug in SQL Server, or am I missing something here?


Additional info:

  • SQL Server 2016 Enterprise

  • Both databases are on the same server, no remote queries or linked servers

  • Static SQL

  • READ COMMITTED Isolation Level

  • Snapshotting is off

  • Can you add all the code involved in this process? Is it a single stored-procedure that does this? – Max Vernon Aug 22 '18 at 16:20
  • The process has several stored procedures, but there's only one where this specific part of the process (and it's dependent parts) takes place. I'll add some more details and code to the question. – Haris Khan Aug 22 '18 at 16:29
  • is it possible the failure occurs when some other part of the process is not yet finished? – Max Vernon Aug 22 '18 at 16:30
  • 1
    Questions: 1) what version and edition of sql server?, 2) what isolation level(s) and what db snapshot mode settings? 3) Is absolutely everything being done with static SQL commands in stored procedures? That is, no client code, no command-line shells, no openrowset, no SQL CLR, no SSIS, no multiple Agent jobs/steps, no linked servers, no service broker, no query notifications and no dynamic SQL using linked servers or logins? – RBarryYoung Aug 22 '18 at 17:45
  • 1
    Try adding a MAXDOP=1 hint to your failing procedure and see if it stops failing. Bad parallelism (really, really bad) is about all I can see that would cause this honestly bizarre failure - and its quite a stretch in its own right. I agree that some form of race condition is the likely cause. Can you get the execution plan and paste it at brentozar.com/pastetheplan - that may give us some hints as well. – Laughing Vergil Aug 22 '18 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.