August 16, 2011 at 12:58 pm
Hi folks,
We have a process that runs via MSDTC heavily and we often get -2 SPID's. They usually clear out and we move along fine. However, the other day, we received a whole lot of blocking, and in capturing the information, we noted a whole bunch of -2 spids which we killed by identifying the UoW's.
My question is, can I automate a process to run every minute or so to identify ALL the -2 spids and kill them immediately? My understanding is that the moment the SPID becomes -2, it has been abandoned, OR does MSDTC try to recover the UoW somehow, and in the process assign a new Spid?
Thanks.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
August 17, 2011 at 12:17 am
are you using a load balancer?
August 17, 2011 at 1:34 am
http://www.sqlserverclub.com/articles/how-to-deal-with-negative-spids-in-sql-server.aspx
Nice Read on the issue your trying to fix
August 17, 2011 at 9:00 am
AlexSQLForums (8/17/2011)
are you using a load balancer?
Alex, no we aren't. I guess a bit more info on the environment which is SQL 2008 Enterprise (x64), two-node cluster.
The application is an in-house developed program in .NET 1 (so it needs to be updated, but controlled by another department, out of our hands). This application talks to a whole bunch of stores all over the country, downloading data frequently throughout the day. I does so by calling an SSIS package that has been tweaked to the max, and pulls in data to our central SQL Server. This application runs in parallel, 10 stores at a time, and when it's done, it cycles through to the next batch. We strongly suspect that updating the code to the most recent .NET will help a lot but I'll need some more definitive proof on that I guess.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
August 17, 2011 at 9:07 am
Jayanth_Kurup (8/17/2011)
http://www.sqlserverclub.com/articles/how-to-deal-with-negative-spids-in-sql-server.aspxNice Read on the issue your trying to fix
Hi Jayanth, I'm afraid this may be necessary if the code fix for .NET doesn't help.
Currently we get anywhere from 0 to 2000 instances of -2 spid's detected, but they clear out, either via SQL or possibly MSDTC from my understanding, which tries to clear up the UoW's. Firing off an alert for -2 Spid's to the on call DBA may trigger lots of false messages. But I guess my question is, can I arbitrarily kill those SPID's BEFORE SQL or MSDTC have had a chance to try to clear them up?
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
August 18, 2011 at 3:38 am
August 19, 2011 at 1:59 am
one thing i would say is that sometimes a -2 spid wont die.
we had a similar situation with biztalk where it issued a dtc connection, became -2 and killing the UoW didn't help, so we had to kill it via the DTC console on the server.
dont know if we just had a dodgy transaction going on etc.
could you not get one of the dev guys to find the UoWs for -2 spids then have them in some way kill them from the DTC console just to be on the safe side if a KILL of the UoW in SSMS doesnt work?
August 23, 2011 at 9:06 am
anthony.green (8/19/2011)
one thing i would say is that sometimes a -2 spid wont die.we had a similar situation with biztalk where it issued a dtc connection, became -2 and killing the UoW didn't help, so we had to kill it via the DTC console on the server.
dont know if we just had a dodgy transaction going on etc.
could you not get one of the dev guys to find the UoWs for -2 spids then have them in some way kill them from the DTC console just to be on the safe side if a KILL of the UoW in SSMS doesnt work?
The killing of UoW's should be trivial (something I could write quickly) and run as a job once a minute/ten minutes/etc. I do have food for thought on this so will look into it further. Thanks everyone for the help.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply