Frozen process

• Roust_m

  SSCoach

  Points: 17405

  More actions

  July 10, 2013 at 11:56 pm

  #279888

  Hi,

  I have a process in "killed/rollback" state for a long time. I found two workarounds for this:

  1. Restart the SQL instance

  2. If the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

  To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

  The fist solution is an easy one, but not desirable. The second is tricky to find out.

  By running this query:

  DECLARE @v1 VARBINARY(64)

  SELECT @v1 = sql_handle FROM sys.dm_exec_requests WHERE session_id = 110

  SELECT * FROM sys.dm_exec_sql_text(@v1)

  I found that the process was executing this command:

  DROP DATABASE MyDBName_dbss -- this is a database snapshot

  At the time when I looked the snapshot did not exist.

  Does SQL Server run something external when dropping database snapshots? If so, how can I determine the process at the OS level?

  Or may be, how can I change the dropping of the snapshots, so I don't end up in a situation like this?

  Thanks.