SQL Server Performance Forum – Threads Archive
Script to kill only connections to specific DBImagin there are two DBs and two applications in production and we need to remove all connections to only one of the DBs. I can stop and start but it recycles both DBs. How can I script to kill only those connections that are connected to specific DB?
If I do it in a loop based on information in sysobjects and Kill those specific connections, is it good approach? CanadaDBA
I think you can see this info in sysprocesses select spid from master..sysprocesses where dbid=db_id(‘DBName’) Now Kill all the spids
Madhivanan Failing to plan is Planning to fail
you can also use sp_who; little programming is required to create a loop based program that can kill all users specific to one db only.
–Display all active processes
EXEC sp_who ‘active’ I have not faced any problem while killing db users.. SQL BOL says: – KILL is commonly used to terminate a process that is blocking other important processes with locks, or to terminate a process that is executing a query that is using necessary system resources. System processes and processes running an extended stored procedure cannot be terminated. Use KILL very carefully, especially when critical processes are running. You cannot kill your own process. Other processes not to kill are: AWAITING COMMAND
Deepak Kumar –An eye for an eye and everyone shall be blind
Can take help of this Q&Ahttp://www.sql-server-performance.com/q&a37.asp link. Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
DECLARE @spid Int
set @spid = 64
KILL @spid Results: Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ‘@spid’. CanadaDBA
CREATE proc rp_kill_db_processes<br />(@dbname varchar(20))<br />as<br /><br />Declare @dbid int,<br /> @spid int,<br /> @str nvarchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />select @dbid = dbid from master..sysdatabases<br /> where name = @dbname<br />declare spidcurs cursor for<br /> select spid from master..sysprocesses where dbid = @dbid<br />open spidcurs<br />fetch next from spidcurs into @spid<br />While @@fetch_status = 0<br /> Begin<br /> Select @str = ‘Kill ‘+convert(nvarchar(30),@spid)<br /> exec(@str)<br /> –print @str<br /> fetch next from spidcurs into @spid<br />End<br />Deallocate spidcurs<br />GO<br /><br />Usage:<br /><br />exec rp_kill_db_processes where ‘test'<br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided â€œAS ISâ€ with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
Hi Luis, I have already tried EXEC @SQLStr but I got the following error: Server: Msg 2812, Level 16, State 62, Line 37
Could not find stored procedure ‘kill 58’.
Oops! I found my mistake. I should use the command this way: EXEC (@SQLStr) Thanks, CanadaDBA