• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Following SQL Query runs successfully but doesn't kill the sessions/processes


I found the following query on Google:

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(<databasename>) AND SPId <> @@SPId


Fairly straightforward however when I run it doesn't work, but the execution window says query run successfully.

How would I go about debugging this? I am running SQL Server 2008 R2

Any assistance is appreciated.

Thank you.
1 Solution
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you confirm that the statement is indeed well-formed? Just uncomment the SELECT (@SQL), comment out the EXEC and then run the statement to confirm.

Next it is possible that by the time your query runs, the processes have ended. KILL will run silently.

Finally, try replacing sysprocesses with newer DMV variants (sys.dm_tran_locks, sys.dm_exec_sessions, or sys.dm_exec_requests) because sysprocess is included purely for backward compatibility.
ZackGeneral IT Goto GuyAuthor Commented:
Thanks using the new DMV variants fixed the issue.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now