none
process suspended status

    Question

  • Hello,

     

    On my sql server 2005 when I run sp_who, I often see process in suspended status. These process block

    my sql server so its utilization is low.

     

    Is there any way to kill suspended process automatically, or can I set any timeoutfor these process?

    Monday, August 13, 2007 12:43 PM

Answers

  • Only if you grabbed the SQL Statement being run before you killed the process with DBCC INPUTBUFFER or a cross apply to sys.dm_exec_sql_text().  I wouldn't recommend killing a process unless you know the ramifications behind doing so, both for the app, as well as the database.  Most often times, the database will enter a rollback state that could take longer than letting the SPID finish its execution.  Suspended processes in SQL Server are suspended because they had to wait on a resource of some sort.  In SQL 2005, you can see the type of wait as well as what the resource it was waiting on in the sys.dm_exec_requests DMV.  Investigate why it is suspended and you might learn of a problem in your database that you need to resolve, which would stop the suspended state processes.

    Thursday, October 23, 2008 3:05 AM
    Moderator

All replies

  • you can right klick on the suspended process in activity monitor and select 'kill process'. then press refresh to make sure it is gone

    Wednesday, January 23, 2008 4:33 PM
  •  LadaM wrote:

    you can right klick on the suspended process in activity monitor and select 'kill process'. then press refresh to make sure it is gone


    hey fellas!! I was wondering if I could reexecute the sql that a suspended process couldnt execute. I ran into an issue where the data file ran out of space and I was wondering if I can re-execute the sql that should've been executed.

    Thx
    Wednesday, October 22, 2008 10:15 PM
  • Only if you grabbed the SQL Statement being run before you killed the process with DBCC INPUTBUFFER or a cross apply to sys.dm_exec_sql_text().  I wouldn't recommend killing a process unless you know the ramifications behind doing so, both for the app, as well as the database.  Most often times, the database will enter a rollback state that could take longer than letting the SPID finish its execution.  Suspended processes in SQL Server are suspended because they had to wait on a resource of some sort.  In SQL 2005, you can see the type of wait as well as what the resource it was waiting on in the sys.dm_exec_requests DMV.  Investigate why it is suspended and you might learn of a problem in your database that you need to resolve, which would stop the suspended state processes.

    Thursday, October 23, 2008 3:05 AM
    Moderator