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 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 SVR 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 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 Recovery to sort out the problem.

Tags: MS SQL Server, sql errors, sql performance, sql recovery, sql tips

Rate This Article: - Excellent Good Average Poor Terrible

Comment on "Removing Transaction Log Files in SQL Server Suspends Database Mirroring"

Your Name

Your Comments

Verification Code: KGPSBC
Enter Code:

Related News: Contained Databases in SQL Server 2012 Managing the Report Content Using Report Manager: Caching Practical Introduction to XML Manipulation Using XQuery in SQL Server Common Table Expressions (CTE) for SQL Server Developers New T-SQL Features in SQL Server 2012