2

I have a problem that I hope you can help me to solve. I'm using SQL Server 2008 R2.

I need to create a test environment in which I'll be able to artificially 'create' lost transactions. Due to network disconnects in the app I'm using, sometimes (very, very rarely) there are 'broken' transactions (some part of the data update goes to DB, but the rest is not which results in incomplete data in database). On my test machine I don't have a clue how to do it.

I know that's the problem with app design but I need to prove that this kind of behaviour is appearing so that the vendor of the app can fix this bug.

I really hope that you can help me out. I'm not a dba - I learn what I can to become one so if this question is 'elementary' please be understanding :)

1

The easiest way is to look into the transaction log and see where there is a sequence that you can 'split'. Start by reading How to read and interpret the SQL Server log to understand what I'm talking about.

Then look into your DB log for a sequence like this:

  lsn1 BEGIN XACT xid1
  lsn2 some update
  lsn3 COMMIT xid1
  lsn4 BEGIN XACT xid2
  lsn5 some update
  lsn6 COMMIT xid2

This sequence shows two operations that occurred in the DB in two distinct transactions. You want to show what happens if xid1 commits, but xid2 rolls back. Since catching the app right in between those two updates is difficult, you can simply restore the DB with STOP AT (see Recover to a Log Sequence Number (SQL Server)) and stop the recovery after lsn3 but before lsn6 (that is, xid 1 commits but xid2 will be forced to rollback). Now you have your DB in a logical inconsistent state (the first operation succeeded, the second failed).

Your Answer

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

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