-
MSSQL 2000 transaction hanging in either commit transaction or killed\rollback state
This is MSSQL 2000 running on a clustered windows server 2003 enterprise service pack 2
I have a user that executes(inside of Query Analyze) a begin transaction on Node A to select/insert into a table on Node b approx 4 records. The begin transaction is used on Node A, and the request is executed on Node A. When the user commits the transaction, the Query Analyzer will hang in the execute state. After hours, the user will close the window. The transaction shows in a "commit transaction" state on Node B. When the SPID is killed, the SPID goes into a killed/rollback state. In both states, the SPID will hang in that state indefinitely. The target table where insert occurred, will not allow a select (NOLOCK not used) and will also hang.The only way to fix the problem (up to this point) is to fail over to the other cluster node. I checked the DTC services for started states on both nodes. The servers are not linked, the query is using full references. I'm not finding any errors.
My database guys are scratching their heads with no solutions. Does anyone here have any ideas?
-
You can't start DTC on both nodes. You need to have DTC as a cluster resource under cluster group with the cluster name and quorum.
DTC should be started by the cluster in the node which owns the cluster group.
-
And have to ebable network access in dtc.
-
sori my mistake..i've received wrong info from my database guys..yes u right dtc can't start on both nodes,in fact dtc already as a cluster resource under cluster group with the cluster name and quorum but the the previous problems still happening...pls someone help us...
-
Can you run this query in query analyzer without any error
begin distributed transaction
select * from sysobjects
rollback
-
In win2k3, you need enable network access for dtc.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|