Removing Transaction Log Files in SQL Server Suspends Database Mirroring

In Microsoft SQL Server 2008, you can configure the database mirroring between two database. It improves database reliability and prevent data loss situations, if one database gets damaged due to any reason. In case of failure, you can restore the copy of your database from mirror database. However, if you unintentionally remove the transaction log files on SQL Server database, database mirroring session is broken by principle server. After this behavior, you can not restore the data from mirror server if principle server fails. It causes database corruption and require SQL Database Recovery to be fixed, if backup is not available.

As a practical instance of this problem, consider the underwritten scenario:

In Microsoft SQL Server 2008 SP1 (Service Pack 1) or Microsoft SQL Server 2008, you configure mirroring between two databases.

You run ALTER DATABASE command along with REMOVE FILE clause for removing the transaction log files of SQL Server database on principal server.

In the above scenario, database mirroring session is aborted and you can not resume it. At the same time, you get the below assertion:

“Location: "logmgr.cpp":3350 Expression: headLFCB->lfcb_startOffset <>GetSize () * (ULONGLONG)PAGESIZE”

Furthermore, the below error message is logged in SQL Server Event Log:

“ spid23s 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.”

Cause

The problem takes place due to wrong log space tracking on mirror database. It can also occur due to Microsoft SQL Server database (MDF file) corruption.

Resolution

If the problem is caused by wrong log space tracking, download latest update on SQL Server and install it. On the other hand, if MDF corruption is causing the problem, you need to go for SQL Server Recovery to sort out the problem.

SQL Recovery is potential through advanced and effective third-party tools, known as MS SQL Recovery software. The applications employ advanced scanning techniques to ensure absolute and quick recovery in all cases of SQL Serverdatabase corruption.