none
Assertion error on mirror server

    Question

  • I am running an environment with 5 databases mirrored.

     

    The principal and mirror server are on the same hardware (HP Server - 8 processor AMD Opteron 2.81 GHz -- 64 GB RAM), and OS (Windows Server 2003 R2 Enterprise x64 SP2). And same build of SQL 2005 x64 (Build 3175).

     

    I am operating the mirroring in "High Performance" mode without a witness server.

     

    Recently, on the largest of the 5 mirrored databases (total size approx 780GB) mirroring was "suspended". The other 4 mirrored DB's were unaffected. I checked the SQL Server logs on both servers. There were no errors related to this on the principal server, but the mirror server showed an assertion error. Here is text from 2 of the log entries:

     

     

    -------------------------------------------------------------------

    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.

    -------------------------------------------------------------------
    Message
    While acting as a mirroring partner for database 'wfs', server instance 'DB-WEB-1' encountered error 3624, status 1, severity 20.
    Database mirroring will be suspended.  Try to resolve the error and resume mirroring.

    -------------------------------------------------------------------

     

     

    Any insight would be appreciated.

     

    Regards,

     

    Wendell Bogle

    Sr. Database Administrator

    Vente, Inc.

     

     

    Wednesday, June 11, 2008 8:56 PM

All replies

  • The way you phrased your question is a little vague.  Are your principal and mirror on the same server or are they running on servers that are running the same type of hardward and operating system?

    By the way, did you run CHECKDB?
    Thursday, June 12, 2008 6:46 PM
  •  

    To clarify, the principal and mirror servers are running on identical hardware, OS and SQL version, but are on 2 distinct servers.

     

    I did NOT run CHECKDB on the mirror instance, is this possible w/o recovering the DB using the appropriate log?

     

    Or, should I be running CHECKDB on the principal?

     

    Regards,

     

    Wendell Bogle

    Thursday, June 12, 2008 6:51 PM
  • You can not run DBCC CHECKDB on the mirror database.

    The best option in this situation is to contact Microsoft Support.

    Sunday, June 15, 2008 8:20 PM
  • -------------------------------------------------------------------

    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.

     

     

    Could you check in the ErrorLog folder , how many mdmp files are created ? Could you report this to Microsoft PSS ?

     

    What happens if you try to resume mirroring ?

    Tuesday, June 17, 2008 4:14 AM
  •  

    On each occurance of this error, one (1) mdmp file is generated.

     

    If I try to resume mirroring, the same set of assertion errors occur on the mirror database/server.

     

     

    Regards,

     

    Wendell Bogle

    Thursday, June 19, 2008 7:40 PM
  • if you could re-setup mirroring the assertion may go away. did you try that ?

     

    Vishal

    Monday, June 23, 2008 9:52 AM
  • Have you been able to come to a resolution for this problem. I think this error is very rare in the wild but I am getting it with a couple different servers. The mirrored application does use sp_getapplock and sp_releaseapplock to control concurrency, and the error is exaclty the same. I found an MS Hotfix for the problem I am thinking of applying but would like to reproduce the error outside of the Production environment first to demonstrate that the Hotfix does indeed work.

     

    Did you try to reproduce the error in a test/lab environment? I want to use the hotfix as opposed to the SP2/CU9 application because this is an extremely sensitive production environment and I don;t want 100 changes to SQL Server, just the right one to fix the problem.

     

    - B

     

    Wednesday, October 1, 2008 6:00 PM
  • I was able to upgrade to v. 3257 (CU 8). This did not correct the issue specified.

     

    I opened a case with MS tech support, and as a workaround, they recommended that I remove the sp_releaseapplock calls from the problem code. As long as the is transaction is explicitly committed or rolled back, this is not an issue. Since making that code change, I have had no production issues with the assertion errors.

     

    WB

     

    Thursday, October 2, 2008 1:08 AM
  • For reference, you can create a snapshot of the mirror database and run DBCC CheckDB on the snapshot.
    Monday, June 21, 2010 8:43 PM
  • Additional note: Mainstream support for SQL 2005 SP2 ended back in January. You may want to upgrade to SP3 as a matter of good practice.
    Monday, June 21, 2010 9:36 PM