SQL SERVER – Orphaned MS DTC Transaction Information

Few days ago, one of our application was crashing IIS application pool because of unhandled exception. After researched we figured out the case of it was orphaned MS DTC transaction.

When multiple connections are operating over one MS DTC transaction, this problem sometime shows up. As many connection are working none of them try to roll back the MS DTC transaction, this creates orphaned connection, which crashes IIS application pool.

You can figure out if there is orphaned connection or not in your application from following quick script. If there are orphaned connection it will show up in result otherwise script will return no results.

SELECT DISTINCT(req_transactionUOW)
FROM sys.syslockinfo
WHERE req_spid = -2

We fixed this issue using Hotfix provided by Microsoft.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – Orphaned MS DTC Transaction Information

  1. Thanks for the tip! Just noticed this today in our dev environment. We, however, held off on applying the fix since it’s not as likely to occur in production and we like to keep all servers at the same SP level.

  2. HI Pinal,

    Apologies for trouble ,but I would like to ask something about Orphans in sql server.
    As I gone through some tutorials/sides, the orphans means the foreign key records from child table which are not available in primary key column in parent table. And we can retrieve this orphans values by left join with parent table.
    But as we know we can’t keep the values in foreign key column in child table that are not exists in primary key column in parent table.

    Please suggest me on this if I am wrong.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s