Ian Kirk - SQL DBA

(Yes, I’m on vacation.  No, I’m not writing this while I’m on vacation - I scheduled it in advance.)

Amongst plenty of other things, Kristine and I spent the week trying to re-create the errors we saw in our production environment.  (Giving credit, where credit is due - this was mostly Kristine’s work)  To our dismay, we haven’t been able to reproduce the errors!

According to Microsoft Support and to the previously mentioned forum post, the culprit is the sp_getapplock / sp_releaseapplock being used within the database.  However, we’ve identified the two stored procedures in the production database that make this call, and we’ve executed them multiple times.  The lock procedure have to be executed within a transaction, so we left a few transaction open, and ran our tests to see if this might help - not at all.

We’ve tried with an empty database, we’ve tried with a restore of the original database.  We’ve tried with manual tests via SSMS and we’ve tried to create the error with people using the application - absolutely no combination of tricks has given us the results we want / expect.  At this point we’re stuck wondering if CU10 will or will not fix our problem.

While I’m away, maybe Kristine will find the magic test that gives us a chance to confirm the fix as well as test the application and then we’ll be ready to move on the patch before the holidays are upon us.  Maybe.

So a week or so ago one of my coworkers stumbled into the fact that we had a database mirroring session that was broken.  We won’t discuss why someone had to stumble into it - that’s being resolved elsewhere.

Anyway, this session had broken, but the log was still building, so it wasn’t the end of the world - let’s just start it back up.  No - we couldn’t be so lucky.  Instead, we found on the target side a log full of minidumps and errors.  Now, for the Googlers (which included me and my teammate!), the errors:
Error: 17066, Severity: 16, State: 1
SQL Server Assertion: File: <loglock.cpp>, line=818 Failed Assertion = 'result == LCK_OK'. This error may be timing-related.
If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Error: 3624, Severity: 20, State: 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption.
To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Error: 1454, Severity: 16, State: 1
While acting as a mirroring partner for database '<DBName>', server instance '<ServerInstanceName> encountered error 3624, status 1, severity 20.
Database mirroring will be suspended.  Try to resolve the error and resume mirroring.

Of course, with a database mirroring target, you can’t fire of a DBCC CHECKDB command - it’s a database in recovery!  Further, as I mentioned earlier, simply restarting database mirroring didn’t change a thing.

Amusingly, this was about 3 days after we’d returned from Seattle, so my first thought was to send out a plea for help from the twitter gang, but after no response, we turned to calling Microsoft.  Actually, most of the work from this point goes to my co-worker Kristine.  She spoke to support, passed the mini dumps and got confirmation of what we’d found in the one reasonable result of our Google search - the use of “sp_releaseapplock” in the source database.

Our source database is a third-party, shrinkwrap peice of sofware for Workforce Management named Aspect.  In a recent upgrade, one or more of the stored procedures for this applicaiton included the use of this particular command.  In turn, that command wrecked our mirroring process.

Just like the Google result above, Microsoft has suggested that CU8 will resolve the issue, but we were already looking for CU10 for this same server, for a different problem, so we’re headed down the route of testing CU10.  Once we’ve confirmed we can make the mirror break happen in test, we’ll see if CU10 is the fix, and I’ll post an update.