Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Solution : Suspect Database – SQL Server detected a DTC/KTM in-doubt transaction with UOW .Please resolve it following the guideline for Troubleshooting DTC Transactions.

Posted by blakhani on January 27, 2015


While testing application which uses distributed transaction, I caused a failure of SQL Server Database. When I performed failover I found that database didn’t recover and failed to come online. It was shown as “Suspect” in SQL Server Management Studio. I checked ERRORLOG and found below messages.

2015-01-22 22:50:33.570    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 0% complete (approximately 24 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-01-22 22:50:33.990    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 2% complete (approximately 24 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
2015-01-22 22:50:34.080    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 0% complete (approximately 130 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-01-22 22:50:34.180    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 11% complete (approximately 5 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-01-22 22:50:35.480    spid15s    Recovery of database ‘MyBadDatabase’ (5) is 44% complete (approximately 2 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
2015-01-22 22:50:37.720    spid15s    10704 transactions rolled forward in database ‘MyBadDatabase’ (5). This is an informational message only. No user action is required.
2015-01-22 22:50:37.820    spid7s    Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2015-01-22 22:50:37.850    spid15s    CHECKDB for database ‘MyBadDatabase’ finished without errors on 2012-01-08 23:41:18.113 (local time). This is an informational message only; no user action is required.
2015-01-22 22:50:38.870    spid7s    Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2015-01-22 22:52:22.990    spid7s    SQL Server detected a DTC/KTM in-doubt transaction with UOW  {6BAC37B8-6515-4FC1-972B-C059D1D5133E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.
2015-01-22 22:52:22.990    spid7s    Error: 3437, Severity: 21, State: 3.
2015-01-22 22:52:22.990    spid7s    An error occurred while recovering database ‘MyBadDatabase’. Unable to connect to Microsoft Distributed Transaction Coordinator (MS DTC) to check the completion status of transaction (0:743395420). Fix MS DTC, and run recovery again.
2015-01-22 22:52:22.990    spid7s    Error: 3414, Severity: 21, State: 2.
2015-01-22 22:52:22.990    spid7s    An error occurred during recovery, preventing the database ‘MyBadDatabase’ (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.

 

As we can see that database was trying to recover. Phase 2 of roll forward is complete and Phase 3 (Rollback phase) encountered an error. The error is cause due to the fact that my bad application uses MSDTC and I caused things to break in MSDTC itself. Ideal way to fix the problem would be to follow KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;306366 (PRB: Errors 3437, 3414, and "Could Not Connect to DTC" Occur When You Start SQL Server)

In my case, I have restarted DTC and I was not able to find UOW anywhere which I need to kill. When I checked sys.dm_tran_locks for column request_owner_guid for any GUID that is non ZERO. But I didn’t find any non Zero (6BAC37B8-6515-4FC1-972B-C059D1D5133E). Here are the steps I did to fix my database.

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE 
GO 
sp_configure 'in-doubt xact resolution', 2 --- presume abort 
GO 
RECONFIGURE 
GO 

Error log showed the change.

2015-01-23 00:45:53.850 spid86       Configuration option ‘in-doubt xact resolution’ changed from 0 to 2. Run the RECONFIGURE statement to install.

In your case, you need to check with application vendor/team to find what needs to be done with In-doubt transactions. Please use appropriate value (I have used 2) and then perform recovery of database.

dbcc dbrecover(MyBadDatabase)

 

Once above steps are performed, I was able to get database recovered. Here is the snippet from errorlog.

2015-01-23 00:46:10.090 spid57       Bypassing recovery for database ID 5. This is an informational message only. No user action is required.

2015-01-23 00:46:10.100 spid57       Starting up database ‘MyBadDatabase’.

2015-01-23 00:47:28.570 spid57       Recovery of database ‘MyBadDatabase’ (5) is 0% complete (approximately 130 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

2015-01-23 00:47:28.670 spid57       Recovery of database ‘MyBadDatabase’ (5) is 1% complete (approximately 7 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

2015-01-23 00:47:29.630 spid57       Recovery of database ‘MyBadDatabase’ (5) is 29% complete (approximately 2 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

2015-01-23 00:47:34.800 spid57       1 transactions rolled back in database ‘MyBadDatabase’ (5). This is an informational message only. No user action is required. 2015-01-23 00:47:34.800 spid57       Recovery is writing a checkpoint in database ‘MyBadDatabase’ (5). This is an informational message only. No user action is required. 2015-01-23 00:47:34.820 spid57       Recovery completed for database MyBadDatabase (database ID 5) in 6 second(s) (analysis 20 ms, redo 3142 ms, undo 3056 ms.) This is an informational message only. No user action is required.

2015-01-23 00:47:34.860 spid57       CHECKDB for database ‘MyBadDatabase’ finished without errors on 2012-01-08 23:41:18.113 (local time). This is an informational message only; no user action is required.

Hopefully this would bring database online if it was caused due to DTC problem. There might be various causes for suspect database and this blog just covers one of them.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server, SQL Server 2012, SQL Server 2014 | Leave a Comment »

    Error : Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.). Please uninstall then re-run setup to correct this problem

    Posted by blakhani on December 16, 2014


    While working for my next blog I made some stupid changes and wanted to start SQL from command line. I have been using net start command to start SQL Services from command line but today I ran the executable directly. The advantage of this method is that information which goes to ERRORLOG is also shown on command line. I browsed to Binn folder and typed executable name sqlservr.exe and hit enter. I was welcomed with Error message as below.

    E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn>sqlservr.exe
    2014-12-16 05:14:43.93 Server      Multiple instances of SQL server are installed on this computer. Renter the command, specifying the -s parameter with the name of the instance that you want to start.

    Since my machine has multiple instances, the error message was quite clear so I added –s SQL2012 and hit enter. and this time, I got more dangerous error message

    —————————
    SQL Server
    —————————
    Your SQL Server installation is either corrupt or has been tampered with (Error getting instance ID from name.).   Please uninstall then re-run setup to correct this problem
    —————————
    OK  
    —————————

     

    Well, error message as interesting information “Error getting instance ID from name” I went ahead and looked at instance name and it was SQL2014. Once I gave sqlservr.exe –sSQL2014 on command prompt, life was good and SQL Server got started.

    If you get similar error, First make sure that instance name is correct. To know the instance name, refer my earlier blog What’s my SQL Server Name?

    Hope this helps.
    Balmukund

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Facebook blakhani
  • Posted in Error, SQL Server | Tagged: , | Leave a Comment »

    SSMS – FIX – Error – The log file is not using Unicode format

    Posted by blakhani on December 11, 2014


    While playing with SQL Server Agent Log setting, I changed some setting and found that I was not able to read SQLAgent Logs any more. Here is the error which I was getting in SSMS.

    I followed my own blog and executed T-SQL to read the Agent log and got same error.

    Msg 22004, Level 16, State 1, Line 13
    The log file is not using Unicode format.

    I looked around into the properties which I changed and one of them was “Write OEM error log”. As per MSDN documentation, this option writes the error log file as a non-Unicode file. This reduces the amount of disk space consumed by the log file. However, messages that include Unicode data may be more difficult to read when this option is enabled.

    And that’s precisely the error message says – “it’s not in unicode”.

    There are two ways to fix the error.

    Method 1 (UI Based)

    • Connect to Object Explorer to SSMS > Expand “SQL Server Agent” >  Right Click on “Error Logs” and choose “Configure”
    • Over there uncheck the box “Write OEM error log”

    Method 2 (T-SQL Based)

    Execute below query

    USE [msdb]
    GO
    EXEC msdb.dbo.sp_set_sqlagent_properties @oem_errorlog=0
    GO
    
    
    

    Both of the methods have same effect. Once done, restart SQL Agent service so that now the file is written in unicode format. You should be able to view the log in the viewer now.

    Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server, SQL Server Agent, SQL Server Management Studio, SSMS | Tagged: , , , , | Leave a Comment »

     
    Follow

    Get every new post delivered to your Inbox.

    Join 914 other followers