Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
My blog post here may be helpful to read: voluntarydba.com/post/2012/11/08/… –  Jon Seigel Jul 8 '13 at 16:41
    
Define "set of commited transactions". In my understanding that would be ONE transaction - with subtransactions? –  TomTom Jul 8 '13 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 '13 at 16:54
6  
Secret feature to selectively rollback committed transactions or logic error in the SSIS workflow... which one is more likely? –  Remus Rusanu Jul 9 '13 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 '13 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.

share|improve this answer
    
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 '13 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. –  Mark Storey-Smith Jul 9 '13 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 '13 at 11:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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