Thursday, October 13, 2011

Incomplete synchronization of mirror database and database corruption in SQL Server 2005

Microsoft has customized its SQL Server over the recent versions to provide administrative ease and a range of new features for improving productivity, efficiency, and availability. It has introduced database mirroring to achieve failover clustering at the database level. Moreover, you can take database snapshots to get instant reports of your SQL database. It allows you to perform safe transactions by maintaining the data consistency during the whole transaction. Inspite of all these benefits, SQL Server users frequently encounter database corruption. This database corruption may be induced due to a number of reasons, such as virus infection, operating system bugs, application errors, etc. As a consequence, all precious database components become inaccessible. To overcome this problem, you need to go for SQL server recovery through a reliable third-party tool.    

For instance, you may come across the following error message while using an SQL Server 2005 database:

“Synchronization of the mirror database, '%.*ls', was interrupted, leaving the database in an inconsistent state. The ALTER DATABASE command failed. Ensure that the mirror database is back up and online, and then reconnect the mirror server instance and allow the mirror database to finish synchronizing.”


The above error occurred due to the failure of “ALTER DATABASE database_name SET PARTNER OFF” statement. Due to failure of this command, the synchronization process of the mirror database was interrupted and thus, the database became inconsistent.

This would prevent you from accessing the database tables and other objects in the database. You can use an updated backup to restore the database. However, if the backup is unavailable, follow the below given resolution steps. 


Try the following steps to resolve the issue:
  • Try to synchronize the mirror database by establishing connection with the mirror server and the principal server.
  • You can also try dropping the mirror database.
  • When you drop the mirror database, use backups to create a new mirror database.

Note: If you don't have backup of mirrored database then don't drop it.

If you still face problems in accessing the database objects, take help of a proficient SQL database recovery tool. These third-party software perform a close recovery of SQL databases to restore all damaged or inaccessible objects, such as tables, views, indexes, constraints, queries, user-defined functions, etc. Furthermore, they support the latest SQL Server versions, including MS SQL Server 2008, 2005, 2000, and 7.0. 

No comments:

Post a Comment