June 15, 2016 at 1:23 pm
Comments posted to this topic are about the item Kill Connections for a specific database
June 20, 2016 at 12:20 pm
Your script is not good enough.
Running it, resulted with the following error messages:
(2 row(s) affected)
[font="Courier New"]Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.[/font]
You cannot kill a system process.
You cannot kill your own process.
No need to join with master..sysdatabases because you already have the dbid in the master.dbo.sysprocesses system view.
You're not CLOSE-ing your cursor in the end.
Can you explain why you're using a global temporary table? What if that global temp table already exists?
You can do the same with a table variable or a local temporary table.
Consider all this stuff and improve your script.
Igor Micev,My blog: www.igormicev.com
July 7, 2016 at 3:18 am
Killing connections like that reminds me of my Sybase days!
How about a one liner?
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
That will drop all user connections apart from your own. Set back to MULTI_USER afterwards.
July 7, 2016 at 6:27 am
a.m 46061 (7/7/2016)
Killing connections like that reminds me of my Sybase days!How about a one liner?
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATEThat will drop all user connections apart from your own. Set back to MULTI_USER afterwards.
Wow, great alternative.
July 7, 2016 at 9:01 am
a.m 46061 (7/7/2016)
Killing connections like that reminds me of my Sybase days!How about a one liner?
ALTER DATABASE [dbname] SET SINGLE_USER WITH ROLLBACK IMMEDIATEThat will drop all user connections apart from your own. Set back to MULTI_USER afterwards.
+1
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 18, 2016 at 3:45 am
I've always used this 🙂
USE master;
GO
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDatabase
SET MULTI_USER;
GO
Regards
Steve
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply