Find suspended state and unlock table in Sql Server

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.

 suspended state in Sql Server

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.

  • Anand

    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.

  • Test

    USE master

    GO

    –sp_who2

    KILL 65