Usually you would just use the Activity Monitor like shown below…
 |
| The mundane repetition of right click each process and selecting Kill Process becomes annoying after the n_th process. |
…buuuuut if you need to restore your Dev Database with fresh data, you need to kill all of the individual connections/processes before attempting a restore. Well when you are doing development work you can have over 10+ connections and using the Activity Monitor is very annoying to close all of them. Therefore I have come up with the following tSQL query:
SELECT dbid, db_name(dbid) as DatabaseName, spid, KillStatement = 'KILL ' + CAST(spid AS varchar(10)) + '; ' FROM sys.sysprocesses WHERE dbid = 15 -- Put your Databases ID here
The idea behind this query is to first find out what you database’s ID is, enter the ID in the where clause and generate the KILL statements. Copy and paste the KILL statements and execute all of them at once. You will be warned about killing your own connection though like this:
Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.
No worries though you can just kill that process in the Activity Monitor or just close the query window.
Enjoy