Blocked by -2 SQL Server 2000 with SP4

Question

Blocked by -2 SQL Server 2000 with SP4

kokila-416295

Say Hey Kid

Points: 663
More actions
August 20, 2008 at 4:29 am

#127112

Hi All,
Before going in to the problem lets go through the SQL Server configuration,
Windows 2000 AdvanceServer with SP4
Active/Active cluster
SQL Server 2000 with SP4
Note- The MSDTC is configured as a Cluster resource rather than a separate group.
Problem:-
In one of the instance of Active/Active cluster we are facing blocking by the SPID -2. This blocking are particularly related to two databases. sometimes out of two databases one database was went into a suspect mode during SQL Server restart, for this we found error message as indoubt MSDTC transactions. These blockings ultimately slows down the performance.
Workaround:
We used to kill UOW for the SPID -2 but nowadays the UOW work for SPID -2 is increased upto 1500, so we are restarting MSDTC whenever we are facing this problem.
Need your assistance to find the permanent solution.
Thanks in advance.
Thanks & Regards,
Kokila K.

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

The permanent solution is to find the source of the indoubt transactions and fix that application to handle its transactions properly.

Are you using Java apps?

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

we've had this as well

always seems to come up on applications that are controlled directly by development and don't go though QA and the regular approval/patch/deployment process and makes us think the devs are secretly updating their apps and then blaming sql server.

for windows 2000/sql 2000 search the KB for the COM+ update. I think it's 37 or 38. KB 922667 is what my patch says.

aryan_naw SSC Rookie Points: 28 More actions · Answer 3

yes we are using java application.

Could please explain me how to find the source of the indoubt transaction and application.

Thanks

kokila

kokila-416295 Say Hey Kid Points: 663 More actions · Answer 4

Dear Gail

we are using java application.

Could please explain me how to find the source of the indoubt transaction and application.

Thanks

kokila

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

you have to get your developers on it

in our case they called BEA and it's a known issue and no help. i think it also has something to do with how they shutdown Weblogic. If it's now shutdown properly you get these in-doubt transactions

kokila-416295 Say Hey Kid Points: 663 More actions · Answer 6

could u pls briefly explain about the same.

Basically i works only under db Engine. not knowing much about application side

thanks

kokila k

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

I had this issue a couple years ago ... All I remember about a negative spid is that it is an orphaned DTC transaction and it was in fact an issue from the application in question, not SQL. I'll see If I can get any info from my old DBA colleague in the mean time.