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 4 posts - 16 through 18 (of 18 total)

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

If everything worked fine in test - what are the differences between test and prod? Creating a linked server is not very difficult, but installing the Oracle software to support it can be a bit tricky.

I don't know whether or not leaving those hung processes will cause any issues. As they are now, they are not blocking any processes - but if they are holding any locks at all it is possible that they will block something.

You really don't have a choice at this point - the only way to clear this is to try Gail's suggestion. If that doesn't work, then a restart of SQL Server will need to be done. Just be aware and prepared for a possible issue when you cycle MSDTC. As I said before - I have seen instances where shutting down MSDTC caused SQL Server to shut down.

This may not happen with a restart - but be prepared just in case.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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 3

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 4

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...