November 21, 2019 at 9:06 pm
Hi Gurus,
Below script kills the only connection related to the AdventureWorks databases. If I want to add another database to kill such as "Northwind' how do I add below?
I tried with :
Set @dbname = 'AdventureWorks' , 'Northwind'
Set @dbname in ( 'AdventureWorks' , 'Northwind' )
but no luck!!
use master
go
Declare @dbname sysname
Set @dbname = 'AdventureWorks'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
GO
November 22, 2019 at 10:50 am
I use this script to kill all connections to a database - modify it to your hearts content
DECLARE @spid int
DECLARE @strsql NVARCHAR(100)
DECLARE curs1 CURSOR FOR SELECT spid FROM master.dbo.sysprocesses WHERE dbid=10
OPEN curs1
FETCH NEXT FROM curs1 INTO @SPID
WHILE @@FETCH_STATUS=0
BEGIN
SET @strsql='kill '+CONVERT (VARCHAR(10),@spid)
--print @strsql --just to check
--EXEC (@strsql) --uncomment this if you want to execute
FETCH NEXT FROM curs1 INTO @SPID
END
CLOSE curs1
DEALLOCATE curs1
don't forget to change the databaseid
MVDBA
November 22, 2019 at 2:03 pm
I think what you are trying to do using the method you are trying is not possible. You cannot store more than 1 value in a variable except a table variable. You could do it with some dynamic SQL which has potential for problems and has some risks, or if you only have 2 databases you want to run it against, just add in a second variable.
If you are wanting to do it on multiple databases, I would put it into a table.
Or use the script provided by MVDBA (Mike Vessey).
Do be careful killing connections though... it will cause the transaction to be rolled back.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply