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 (https://blog.sqlauthority.com)
5 Comments. Leave new
Great man!
It will really help the job seekers.
good luck and continue ur great work.
Hi,
I’m from Argentina. Do you know if this hotfix is still available? I can’t download it.
Thanks !
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.
Every article is one valueble step to learn SQL in this site.
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.