Some time when SQL query process goes in suspended state, it automatically locks the table and we cannot do any other operation on this table. If this process take much time and the result is not valuable then we need to wait until it finished.
So in following example we can learn how to find suspended process and how to kill that and unblock table.
In Sql Server management studio first Select Database name and expend it
Goto Database–>Views–>System views–> dm_exec_requests
Run this view and it will show you all process of your Sql server.
In status column if you find any process state “Suspended” then this can be the cause of table lock.
How to Unlock table in this case:
Find Session id of the suspended process
Write following command on sql query browser and execute.
Kill <Session_ID>
This command will terminate your sql process and your table will be unlocked.
I am seeing more than 150 Suspended DB connection in my SQL server 2005 and when it reached 150 + my web applications becomes very very slow, then i kill all the process which has lower priority manually, after doing this operation – my application become fast and works nicely.
Could you please help me to figure out –
A. Why more suspended connection is generating in 15 minutes only?
B. Why suspended connection is causing web application performance badly?
C. What is harm , if suspended connection is more in DB server?
Please address all these three scenario.
USE master
GO
–sp_who2
KILL 65