MS DTC and sys.dm_tran_active_transactions

A  sql reindex command was being blocked by two active transactions in MS DTC.   Use

sys.dm_tran_active_transactions to report more detail on transactions

 
 

 

select * from sys.dm_tran_active_transactions where transaction_uow  = '922D5B60-A5CA-4C96-8891-703021B40AD8'

/*Returns: 

Transaction_id = “1770489134”	
Name = “DTCXact”
Transaction_begin_time =”2011-10-29 11:01:39.517”
Transaction_type = “4”
Transaction_uow=”922D5B60-A5CA-4C96-8891-703021B40AD8”
Transaction_state=”2”
Transaction_status=”12”
Transaction_status2 =”451
Dtc_state=”0”
Dtc_status=”0”
Dtc_isolation_level= “1048576”
*/

 

SQL Server Books Online has full details only all columns and values returned > For analysis purposes , I’ll highlight a few:

 Transaction_type = “4”  means it’s a distributed transaction

Transaction_state =”2”  means the transaction is active

 As a follow up I execute:

select * from sys.dm_tran_locks  where request_owner_guid = '922D5B60-A5CA-4C96-8891-703021B40AD8'

 This returns transactions  related to this owner guid.

If you see -2 in the request_session_id column , investigate further for orphaned distributed transactions.

Related Posts

SQL Server detected a DTC/KTM in-doubt transaction with UOW

Author: Jack Vamvas (http://www.sqlserver-dba.com)

TCP Chimney offload

On Windows 2003 Enterprise Edition Service Pack 2 TCP Chimney was enabled by default. It took me a while to discover this feature. But once I did find it , I turned it off.

It was causing numerous problems on many applications , particularly modelling based , long running SQL Server transactions.  I also experienced problems with Microsoft Exchange.

Windows 2008 still has the TCP chimney offload functionality but the command syntax has changed.  On the article there is a command that shows the status & it seems that the chimney is disabled by default anyway.

 http://support.microsoft.com/kb/951037

The script to disable tcp chimney is :

 

netsh int tcp set global chimney=DISABLED

Excel Connect to SQL Server

Excel connect to SQL Server

Connecting to SQL Server from Excel is an easy process. 
Users want to distribute an Excel document , for example , within
a network. Particuarly as there are plenty of libraries the user can access from Excel
as a presentation layer.

To implement this solution you will need :

Microsoft Excel
Visual Basic Editor

Step 1: Create your Excel workbook
Step 2: In Visual Basic Editor create a new project
and within Tools | References , select "Microsoft ActiveX Data Objects Library"
Step 3: Insert a module into your new project , and create a subprocedure
Step 4: Within that sub procedure copy and paste the follwoing code:

 

 

 

####################Start Code##########################

' Create a connection object.
Dim cnMyDB As ADODB.Connection
Set cnMyDB = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=MyDB;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnMyDB.Open strConn

' Create a recordset object.
Dim rsMyDB As ADODB.Recordset
Set rsMyDB = New ADODB.Recordset

With rsMyDB
' Assign the Connection object.
.ActiveConnection = cnMyDB
' Extract the required records.
.Open "SELECT * FROM MyTable"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsMyDB

' Tidy up
.Close
End With

cnMyDB.Close
Set rsMyDB = Nothing
Set cnMyDB = Nothing

########################End Code################################### 

Step 5:  Run the code , and check your Excel worksheet
Step 6 : If you have problems , check connection and permissions

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server detected a DTC/KTM in-doubt transaction with UOW

There was an unexpected server shutdown , on a sql server box . Upon restart one of the databases went into an IN RECOVERY state ,
and once the database had reached 99% complete  the following message appeared in the SQL Server log files .

SQL Server detected a DTC/KTM in-doubt transaction with UOW  {63E15310-78DA-42DA-8BB1-38B59FDA866E}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

Initially I tried the  : KILL '{63E15310-78DA-42DA-8BB1-38B59FDA866E}' WITH COMMIT   ,but this didn't resolve the issue.

The resolution was achieved by :

1)  Type "mmc.exe" (without the quotation marks) in the Run dialog box to open Microsoft Management Console (MMC).
2)  In MMC, on the Console menu, click Add/Remove Snap-in.
3)  In the Add/Remove Snap-in dialog box, click Add.
4)  In the Add Standalone Snap-in dialog box, click Component Services, and then click Add to add the snap-in.
5)  Click Close.
6)  In the Add/Remove Snap-in dialog box, click OK.
7)  In the console root under Component Services, expand Component Services, point to Computers, point to My Computer, and then click Distributed Transaction Coordinator.
8)  Select Transaction List. You see transactions in the details pane. Right-click the transaction, and then click Resolve. You now have three options: Commit, Abort, or Forget. Choose the one that is appropriate for your situation.
Author: Jack Vamvas (http://www.sqlserver-dba.com)

How to request SQL Server troubleshooting

When requesting SQL Server troubleshooting ,don’t send  messages such as “users are complaining of sudden slow performance “ or “this was OK yesterday, but is suddenly very slow” . Usually it’s insufficient to solve the problem. It’s not impossible, but the process slows down.

Detailed information is better. I analyse the problem quicker, you get a solution quicker . Examples of useful information are :

1)Database  server and OS server version.

2)Application related details. For example, BACKUPS are very slow.

3)SQL DDL , meaning Data Definition Language. Normally , this is a CREATE TABLE script .

Using SQL Server Management Studio create  DDLs

 

4)DML meaning Definition Markup Language . The SQL statements.

5)Data sample . In a format easy to import , this could be a CSV or an INSERT statement

6)If you don’t think it’s a DDL or DML root cause – then send as much detail as possible, such as recent configuration changes, fix packs, error logs –OS and Database Server.

7)Often the problem is a vague performance issue logged by the end-user. These require  SQL Server database troubleshooting  – where an experienced  DBA can work through the problem and resolve the problem .

If you need assistance in generating detailed information , contact me .