question

sachitanand avatar image
sachitanand asked

Multiple sessions of Specific Database needs to be Killed

I have an Database RMG_APPS Now i Need to kill all the Sessions on the server for RMG_APPS. Sessions which are sleeping or Suspended. Important: All Such sessions should be killed in one go. I dont want to write Kill Session ID every time. Thanks in advance
sessions
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
ThomasRushton avatar image
ThomasRushton answered
Something like this: DECLARE @KillCommand varchar(2000) SELECT @KillCommand = '' SELECT @KillCommand = @KillCommand + 'KILL ' + convert(char(15), spid) + ' ' FROM sysprocesses WHERE dbid = db_id(@DBName) IF @KillCommand <> '' BEGIN PRINT @KillCommand EXEC (@KillCommand) END This is the command I used on SQL Server 2000 and up when doing mass restores. Should work for you. Nicked from my [blog post on bulk restoring multiple databases][1]. You might want to change the `varchar(2000)` to `varchar(max)` and you will want to change `@dbname` to the name of your database... [1]: https://thelonedba.wordpress.com/2013/01/24/bulk-restores-response-to-a-question-on-ask-sqlservercentral/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Notes to self: * Update blog post to use CHAR(13) + CHAR(10) * Put semicolons in code when updating blog post.
0 Likes 0 ·
sachitanand avatar image sachitanand commented ·
Hi Thomas, Thanks For The Your Solution. But What About The Sessions which are in Active or Runnable Mode. I only want to kill The sessions which are sleeping or Suspended
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.