application hang on spid blocking

Question

application hang on spid blocking

Bhrett Glenn

SSC Enthusiast

Points: 180
More actions
August 14, 2007 at 10:58 am

#175985

I have a vendor application that is timing out (server unavailable) this is happening more and more often. It used to correspond with SPID blocking,(have to manually kill SPID because app will not release) Now I see now SPID blocking and it is still occurring. ping time to server are great, no errors in the logs. HELP

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

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

Bob Fazio SSChampion Points: 10794 More actions · Answer 1

"SPID Blocking" as you call it is caused by an operation in an open transaction that has not either commited or rolled back. This is an application issue. There is no way to resolve the problem other than the way you are doing it.

You might be able to talk with the vendor to see why this transaction is not closed, but my guess is a user is using the application wrong. (like not pressing a button of some sort when they are done doing an edit).

Basically your killing of the session is un-doing what the session was attempting to do, but didn't finish.

Bhrett Glenn SSC Enthusiast Points: 180 More actions · Answer 2

is there anyway in SQL to set a time limit on blocking processes to have them automatically release after a specific time period. I have to do it this way to keep from voiding my agreement with the vendor. One other note, this software is installed in about 200 locations and there are only 3 sites that this is occurring at is there any thing else that could cause it?

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 3

you might want to try running profiler over the database to try to figure out what user / procedures are causing the blocking.

Then find out how the user is using the app.

Perhaps they are have a message window popping up and waiting for an answer that is inside a transaction (app issue) and they go to lunch or something.

Bhrett Glenn SSC Enthusiast Points: 180 More actions · Answer 4

It actually happens when they are actively using the application, this app has no pop up prompts. I have run profiler, and it isn't limited to any particular user, or procedure , because it happens very erratically. It may go 2-3 weeks between problems. That is why this is so frustrating. I have other SQL server apps that run with no issues at all.

Bob Fazio SSChampion Points: 10794 More actions · Answer 5

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'blocked process threshold', 20 ; -- 20 seconds (or to whatever)
GO
RECONFIGURE
GO
This will force a report to be generated.

You can also configure alert to be generated.

This will NOT disconnect the offender.

Just create an notification of the issue.

Also check out this link

http://support.microsoft.com/kb/298475

Bhrett Glenn SSC Enthusiast Points: 180 More actions · Answer 6

Bhrett Glenn

SSC Enthusiast

Points: 180

August 15, 2007 at 11:26 am

#727129

I'll give that a shot thanks!