Is it possible for an entire set of committed transactions to be rolled back in SQL Server 2008 R2 without any notification or logging anywhere?

We had a set of data apparently get rolled back this weekend on an HA cluster SQL Server 2008 R2 environment. Data existed and now it doesn't. Nothing in the SQL Server error logs, nothing in the cluster logs. SQL Server agent job that originally loaded the data ran successfully (including progress emails sent from the processes themselves).

The only thing that wasn't rolled back was the bulk inserted data. The database is set to full recovery, so bulk inserted data SHOULD have been rolled back as well, correct?

Sorry for the lack of details but I am THOROUGHLY stumped. Can't even think where else to LOOK.

  • My blog post here may be helpful to read: voluntarydba.com/post/2012/11/08/…
    – Jon Seigel
    Jul 8, 2013 at 16:41
  • Define "set of commited transactions". In my understanding that would be ONE transaction - with subtransactions?
    – TomTom
    Jul 8, 2013 at 16:42
  • It's a set of nested SSIS pkgs and stored procs; each individual piece of the load has its own transaction wrapping it, with the exception of the bulk inserts. There are no nested transactions.
    – Valkyrie
    Jul 8, 2013 at 16:54
  • 6
    Secret feature to selectively rollback committed transactions or logic error in the SSIS workflow... which one is more likely? Jul 9, 2013 at 7:41
  • SSIS workflow with no changes for months, and rollbacks that happen spontaneously: normally I'd be looking at SSIS too but it just doesn't compute. If a logic error exists for something that runs daily for months or years at a time, it would have shown up before now. I've gone through it and cannot find anything that would've been triggered these two random times. Hoping the t-log will shed some light but so far no dice.
    – Valkyrie
    Jul 9, 2013 at 12:12

1 Answer 1


As Remus succinctly puts it, there is no secret feature to rollback a transaction without logging. It is also not possible to rollback a committed transaction.

Your code is broken, not SQL Server.

From the scant details the only scenario I can envisage is the now missing data having been inserted as one long running distributed transaction, someone sighting the data via an uncommitted read and the transaction subsequently being rolled back.

Most likely is an error in the logic or analysis of the failure. Human error and cover-up perhaps even more so.

  • Would t'were that it were so, but this is an automated system that only a select few have access to, and fewer have the ability to make changes to. And it happened again last night between 11 p.m. and 2:30 a.m. Is it possible that a corruption in the t-log would trigger a rollback of committed transactions? 'Cause these are committed, and they are rolling back.
    – Valkyrie
    Jul 9, 2013 at 12:08
  • 1
    They are not rolling back in the BEGIN TRANSACTION ... COMMIT TRANSACTION context of a transaction. Leave a server side trace running overnight and get some evidence for what's happening because it certainly isn't the voodoo bug you currently think it is. Jul 9, 2013 at 15:49
  • Trace didn't show anything. I moved the t-log to a different drive and then added a new one and shrunk the current one down to nothing with no growth allowed so it essentially wouldn't be used. So far so good; unfortunately it's one of those 'have to define a positive with a negative' situations.
    – Valkyrie
    Jul 12, 2013 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.