February 9, 2015 at 8:18 pm
Comments posted to this topic are about the item Kill Processes
February 10, 2015 at 5:32 am
won't you get an error if you try to kill system processes?
February 10, 2015 at 7:43 am
The script can be handy as a starting point, if you want to kill multiple connections for a specific application or user. You must be careful to filter on Login Name and/or Program Name.
However, if what you're really wanting to do is kick out all user connections so you can perform a maintenance operation, then the following is more practical. The following will kill all user processes for a specific database, allow 2 minutes for open transactions to rollback while disallowing new connections, and then set database to resticted_user (sysadmin only) mode.
ALTER DATABASE <database> SET RESTRICTED_USER WITH ROLLBACK AFTER 120 SECONDS;
Once you are ready to resume allowing user connections to the database, perform the following.
ALTER DATABASE <database> SET MULTI_USER;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 10, 2015 at 7:49 am
Our main purpose for something like this is to do a database restore. Which method of killing processes do you prefer to do that?
February 10, 2015 at 8:27 am
Robert.Sterbal (2/10/2015)
Our main purpose for something like this is to do a database restore. Which method of killing processes do you prefer to do that?
ALTER DATABASE <database> SET RESTRICTED_USER WITH ROLLBACK AFTER 120 SECONDS;
When it finishes rolling back all active transactions and disconnecting each session, the database is left in sysadmin only mode.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 17, 2015 at 6:23 am
Igor Micev (2/16/2015)
bgrossnickle (2/10/2015)
won't you get an error if you try to kill system processes?This is a good notice. System databases' process should be avoided.
@author: What about if you want to kill the current session from itself?
SQL Server won't allow the DBA to KILL herself, but she can avoid failed attempt by specifying filter:
WHERE SPID != @@SPID.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 17, 2015 at 6:36 am
Thank you all for your comments.
As I said in the script it must be used carefully. Often I use it because programming errors, some applications doesn't manage their connections in the right way and they leave them alive.
In this part I can filter the process that I want to kill. For example database, login or status.
SELECT spid
INTO #toKill
FROM dbo.#process p
WHERE p.dbname = 'database'
Of course, the script can be improved to avoid some processes than we don't want to touch.
Thanks
February 17, 2015 at 8:09 am
Eric M Russell (2/17/2015)
Igor Micev (2/16/2015)
bgrossnickle (2/10/2015)
won't you get an error if you try to kill system processes?This is a good notice. System databases' process should be avoided.
@author: What about if you want to kill the current session from itself?
SQL Server won't allow the DBA to KILL herself, but she can avoid failed attempt by specifying filter:
WHERE SPID != @@SPID.
That's exactly what I wanted to point out.
Igor Micev,My blog: www.igormicev.com
February 17, 2015 at 10:13 am
'Use it carefully'
Understatement of the year!
February 17, 2015 at 10:40 am
It's February, I'm sure will be more jeje
I really understand your point, I'll make something about it.
February 17, 2015 at 10:42 am
Kev Charlatan (2/17/2015)
'Use it carefully'Understatement of the year!
Yeah, you definately want to first test fire it on the development server a few times just to make sure you know how to to hit only your intended targets.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply