June 23, 2016 at 6:47 am
DB: SQL Server 2008 :
I right click on db server . I get Activity Monitor
In the processor tab , I get multiple process of my database instance.
I want to kill all process.
I can kill one by one ....is there any way so that I can select all and kill all process of my db instance ?
I don't see "select all" menu
June 23, 2016 at 6:56 am
The following statement can be used to kill a specific connection:
KILL <SPID>
If what you're trying to do is terminate all connections on a specific database, while allowing a couple minutes for active transactions to complete, and deny new connections to this database from non sysadmin users, then you can do the following. This is typically done when you need to perform maintenance on database. This can also be applied to all databases if you need to gracefully free up the entire server.
ALTER DATABASE <DATABASE_NAME> SET RESTRICTED_USER WITH ROLLBACK AFTER 120 SECONDS;
http://www.blackwasp.co.uk/SQLRestrictedUser.aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 23, 2016 at 8:06 am
I am doing through UI.
right click > Kill Process
June 23, 2016 at 8:25 am
From SSMS Object Explorer, you can also right clicks on a database and set the 'Restrict Access' propery from the default 'MULTI_USER' value 'RESTRICTED_USER', which will achieve the same result as the T-SQL command I mentioned above. If you choose to do this, then remember to set it back to 'MULTI_USER' when it's time for users to start re-connecting again.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply