Search

Tuesday, December 21, 2010

Using MSDTC As a Non-clustered Resource Cause SQL Server Database Corruption

In Microsoft SQL Server, MSDTC (Microsoft Distributed Transaction Coordinator) allows the client applications to include various data sources in a single transaction. These transaction coordinates committing distributed transactions throughout all servers, which are listed in transaction. MSDTC is a transaction manager that is actually a Windows process. Under some situations, this transaction manager may cause various critical problems in SQL Server, if it is improperly configured, which cause database corruption. In such cases, you need to go for SQL data recovery solutions to get your mission-critical data back.

Microsoft supports running the MSDTC services only on the cluster nodes as clustered resource. You are not recommended to run the MSDTC in a stand-alone mode on cluster. It is not even supported in most of the cases. Using the MSDTC as non-clustered resource on Microsoft Windows cluster can cause several problems. Such configuration is quite problematic as the transaction can not be orphaned and you might come across database corruption in any cluster failover takes place.

Rebuild MSDTC on MS SQL Server failover clustered server:

In order to rebuild the failed MSDTC and perform SQL server recovery, go through the underwritten steps:

  • Switch all the SQL Server services to the Manual mode.

  • Close the Control Panel and reboot server. It clears memory of the DLLs, which are loaded by SQL Server services. Else, such services remain in server memory and hold the locks on Server Server system resources.

  • Remove the following registry keys: HKEY_CLASSES_ROOT\CID HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet0\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet1\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet2\Services\MSDTC HKEY_LOCAL_MACHINE\SYSTEM\ControlSet3\Services\MSDTC

  • Delete Z:\MSDTCLog directory.

  • Remove all DLL files.

  • Run Dtcsetup.exe on first node.

  • Run Dtcsetup.exe on second node, if prompted.

  • After clicking OK button on second node, you can click OK button on first node, and reboot your system.

The above method can rebuild MSDTC in most of the cases and fix related problems. However, if the problem is caused by database corruption due to use of MSDTC on a non-clustered resource, it does not work. In such critical situations, an updated backup proves great help. However, if backup is not current, MS SQL repair becomes need of hour.

SQL recovery is potential through powerful and advanced third-party applications. Such tools are powerful enough to methodically scan entire database and extract all inaccessible data from it. They are quite safe and easy to use.

1 comment:

  1. SQL databases can be restored owing to sql server database repair. It uses modern interface with step-by-step recovery wizard. Tool starts under all popular Windows OS. It has low system requirements.

    ReplyDelete