Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><em>The following is based off the assumption that your database is in full recovery mode and you do not perform any restores or other trickery during your tests (such as detaching/reattaching the db, etc).</em></p> <p>Here is a fairly tedious approach to investigating your problem, but is guaranteed to provide the data needed to figure this out.</p> <ol> <li><p><strong>Take a Full Backup of the database</strong> Do this right before starting the test suite. We're going to be restoring the database so also make sure you've got enough disk space for 2-3 copies of the database files.</p></li> <li><p><strong>Create a Sql Profiler trace</strong> For events, select RPC Starting/completed, sql Batch Starting/completed, Sql Statement starting/completed, SP Statement starting/completed, TM:* completed, SQLTransaction, DTCTransaction, and user error message. Capture all the columns. </p></li> <li><p><strong>Reproduce the Issue</strong> Run the minimum number of tests to produce a failure. Let the tests finish so you capture all the cleanup code, then stop the profiler trace.</p></li> <li><p><strong>Take a transaction log backup</strong> We may need this for point-in-time restores later.</p></li> <li><p><strong>Locate the failure in the trace</strong> If you're getting a primary key failure, then it should be easy to track down, just look for the User Error Message. Write down the <em>exact</em> time the error occurred.</p></li> <li><p><strong>Examine the trace for obvious issues</strong> Start from the error and work backwards until you find the start of the test that failed. Write down the <em>exact</em> time the setup started for the last failed test. Examine all the sql in this range. Is the sql <strong>exactly</strong> what you expect? Are the row counts correct? Is the transactionId correct? (The transactionId column should be different for every statement <em>not</em> in a transaction, and the same for every statement <em>inside</em> a transaction). If you have mis-matched BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN's, the transactionId will let you know. </p></li> <li><p><strong>Restore the DB to right before the failed test setup</strong> Restore it to a new database so we can compare the original and the copy. First restore the full backup using "RESTORE DATABASE .... WITH NORECOVERY". Then restore the transaction log backup using "RESTORE LOG .. WTIH STOPAT, RECOVERY" and specify a time immediately before the failed test setup.</p></li> <li><p><strong>Verify the database state</strong> Check for test data that may not have been cleaned up. Is everything as it should be? If not, you can restore the database again to an earlier point. You're looking for a point in time just before a test starts where the database is in a good, known state.</p></li> <li><p><strong>Restore the DB to right before the error occurred</strong> If you have room, restore to another new DB. Check for the data that caused the PK violation. Would the error occur if you ran the problematic statement again? Verify that it does or doesn't occur.</p> <ul> <li>If it doesn't occur, your problem is likely mis-matched transaction handling. If you were missing a COMMIT earlier, you may have had a transaction still open. When you restore with STOPAT, any un-committed transactions would be rolled back. This would also explain how running the tests individually works, but together they fail.</li> <li>If it does occur, then work backwards until you find the issue. You may need to restore the DB multiple times before you figure it out. Your process will be Restore DB, examine trace, examine data, restore to different point, examine trace, examine data, etc.</li> </ul></li> <li><p>If after all this you are still at a loss, then you may want to investigate using database snapshots as part of your unit tests. Basically, create the db snapshot, setup and run test, teardown is replaced with a reverting the database back to the snapshot. This will guarantee an identical database before and after each test.</p></li> </ol> <p>2012 Management Studio has an improved database restore wizard that makes the point in time restores very easy. <strong>Good Luck!</strong></p>
    singulars
    1. This table or related slice is empty.
    1. POMSTest Unit tests (VS 2012) vs. SQL Server, intermittent Primary Key collisions
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USStrayCatDBA
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMSTest Unit tests (VS 2012) vs. SQL Server, intermittent Primary Key collisions
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWell, I've been going through these steps and I have reduced the amount of failures significantly. I have found some cascaded deletes that weren't taking place combined with some poorly-conceived test data. Thanks for the extremely detailed and helpful info. I'm going to mark this as the answer.
      singulars
      1. PO
      1. USFinster
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload