How to Removing SQL 2005 process command in KILLED/ROLLBACK status waiting on MSQL_DQ

Question

How to Removing SQL 2005 process command in KILLED/ROLLBACK status waiting on MSQL_DQ

Viewing 3 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Jeffrey Williams-493691 (8/3/2011)
GilaMonster (8/3/2011)
Check in DTC what the state of the transaction is. Also, try a restart of DTS (not SQL, not the machine, just MSDTC), see if that fixes anything.
Gail, I would be very careful with suggesting a restart of MSDTC. I have seen instances where doing so causes SQL Server to shutdown because MSDTC is a required resource for SQL Server.

DTC shouldn't be required for SQL. Maybe some people set up clusters with it as a dependency, I don't know.

I've certainly done that before (in a clustered environment) without any impact on SQL Server. That was SQL 2000 and 2005.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

GilaMonster (8/4/2011)
Jeffrey Williams-493691 (8/3/2011)
GilaMonster (8/3/2011)
Check in DTC what the state of the transaction is. Also, try a restart of DTS (not SQL, not the machine, just MSDTC), see if that fixes anything.
Gail, I would be very careful with suggesting a restart of MSDTC. I have seen instances where doing so causes SQL Server to shutdown because MSDTC is a required resource for SQL Server.
DTC shouldn't be required for SQL. Maybe some people set up clusters with it as a dependency, I don't know.
I've certainly done that before (in a clustered environment) without any impact on SQL Server. That was SQL 2000 and 2005.

+1 I never had issues with SQL Server when cycling MSDTC.

Linked server queries often end up in this inconsistent status (KILLED/ROLLBACK, but never ends). You'd better take a look at the locks owned by the process and decide what to do. Those "ghost" processes can easily cause blocking, so you'd better decide now whether a service restart is worth.

-- Gianluca Sartori

timr-1113026 SSC Veteran Points: 276 More actions · Answer 3

We ran into this same issue after setting up a linked server to our Active Directory. The issue was that we had 140 users running queries against it. It will ocassionally end up in with hung up transactions. Our solution was to run a nightly update into a permanent SQL table so we only have one nightly transaction instead of thousands of individual transactions throughout the day.

This fixed the problem...