To Kill any logins before doing for example a failover use stored proc sp__kill_all:
e.g.
sp__kill_all
or
sp__kill_all ‘kill_m_all’ — which kills all the user logins on the Server.
If you just want to kill Logins for a particular database, if you are loading into it for example, then there is another stored proc sp__kill_db
e.g
sp__kill_db
.
create procedure dbo.sp__kill_all
/* Copyright (c) 2003 Rob Verschoor/Sypron B.V. */
@p_login varchar(30)
as
begin
declare @p int, @cmd varchar(12), @suid int
declare @kpid int, @spidcmd varchar(16), @status varchar(12)
declare @login varchar(30), @xactyn varchar(3)
declare @rc int, @err int
set nocount on
if proc_role(“sa_role”) = 0
begin
print “You need ‘sa_role’ to run this procedure.”
return 0
end
if @p_login = “?”
begin
print ” ”
print “Usage: sp__kill_all { login_name | ‘kill_m_all’ }”
print “”
print “This procedure kills multiple ASE processes as specified:”
print ” ”
print “Arguments:”
print ” login_name – kills all processes for this login name”
print ” ‘kill_m_all’ – kills all processes”
print “”
print “Copyright (c) 2003 Rob Verschoor/Sypron B.V.”
print “Visit http://www.sypron.nl”
print ” ”
return 0
end
select @suid = NULL
if @p_login != “kill_m_all”
begin
select @suid = suser_id(@p_login)
if @suid = NULL
begin
print “‘%1!’ is not a valid login name”, @p_login
return 0
end
end
select spid, kpid, status, cmd, suid, tran_name
into #to_be_killed
from master..sysprocesses
where suid = isnull(@suid, suid)
and suid != 0
and spid != @@spid
select @rc = @@rowcount, @err = @@error
if @p_login != “kill_m_all”
print “Found %1! spids for login ‘%2!’”, @rc, @p_login
else
print “Found %1! spids”, @rc
/* Adaptive Server has expanded all ‘*’ elements in the following statement */ select #to_be_killed.spid, #to_be_killed.kpid, #to_be_killed.status, #to_be_killed.cmd, #to_be_killed.suid, #to_be_killed.tran_name into #tmp_2bkilled
from #to_be_killed
while 1=1
begin
set rowcount 1 — process row by row
select @p = spid, @kpid = kpid, @spidcmd = rtrim(cmd),
@xactyn = case tran_name when NULL then “no” when “” then “no” else “yes” end,
@status = rtrim(status), @login = suser_name(suid)
from #tmp_2bkilled
if @@rowcount = 0 break — exit loop when ready
delete #tmp_2bkilled where spid = @p
set rowcount 0
print “Killing spid %1! (login ‘%2!’; %3!,%4!; active Xact:%5!)”, @p, @login, @status, @spidcmd, @xactyn
select @cmd = “kill ” + str(@p, 5)
exec(@cmd)
— In pre-12.0, CIS can be used instead of exec-immediate. See page 60 in the book
— “Tips, Tricks & Recipes for Sybase ASE” (www.sypron.nl/ttr)
–exec sp_remotesql SYB_ALIAS, @cmd
end
set rowcount 0
— wait a sec and see who’s still there
waitfor delay “00:00:01”
select t.spid, t.kpid, t.status, t.cmd, t.suid
into #still_there
from master..sysprocesses sp, #to_be_killed t
where sp.spid = t.spid
and sp.kpid = t.kpid
select @rc = @@rowcount, @err = @@error
if @rc != 0
begin
print “”
print “%1! killed processes are still around:”, @rc
select spid, suser_name(suid) login, cmd, status
from #still_there
order by spid
print ” ”
print “Wait a while until these processes have disappeared…”
end
end
go
.
.
create procedure dbo.sp__kill_db (@DATABASE varchar (30)) as
set ansinull on
set close on endtran on
set flushmessage on
set nocount on
set string_rtruncation on
if (@@trancount = 0)
begin
set chained off
if (@@isolation > 1)
begin
set transaction isolation level 1
end
end
else
begin
print ‘ sp__kill_db CANNOT BE RUN FROM WITHIN A TRANSACTION.’
print ‘ ‘
return 1
end
declare @cmdstring varchar (255),
@errorchar varchar ( 4),
@id int,
@login varchar ( 30),
@spid smallint,
@spidchar varchar ( 11)
print ‘ ‘
declare killdb_cursor cursor for
select spid, suser_name (suid)
from master.dbo.sysprocesses
where dbid = db_id (@DATABASE)
and dbid > 1
and spid @@spid
order by spid
for read only
if (charindex (‘sa_role’, show_role ()) > 0)
begin
if (@DATABASE = ‘master’)
begin
print ‘ YOU CANNOT EXECUTE sp__kill_db FOR DATABASE master.’
print ‘ ‘
return 1
end
else if (db_id (@DATABASE) IS NULL)
begin
print ‘ DATABASE %1! DOES NOT EXIST.’, @DATABASE
print ‘ ‘
return 1
end
else
begin
open killdb_cursor
fetch killdb_cursor into @spid, @login
if (@@sqlstatus = 2)
begin
print ‘ THERE ARE NO ACTIVE PROCESSES FOR DATABASE %1!.’, @DATABASE
end
else
begin
print ‘ SPIDS WILL BE KILLED FOR DATABASE %1!.’, @DATABASE
print ‘ ‘
while (@@sqlstatus = 0)
begin
select @spidchar = convert (varchar (11), @spid)
select @cmdstring = ‘kill ‘ + @spidchar
print ‘ KILLING SPID %1! FOR LOGIN %2!.’,
@spidchar, @login
execute (@cmdstring)
fetch killdb_cursor into @spid, @login
end
print ‘ ‘
if (@@sqlstatus = 2)
begin
— execute sp_status @PARM=@DATABASE
print ‘ ALL APPROPRIATE PROCESSES HAVE BEEN KILLED.’
end
else
begin
select @errorchar = convert (varchar (4), @@sqlstatus)
print ‘ CURSOR ERROR. STATUS = %1!.’, @errorchar
return 1
end
end
close killdb_cursor
deallocate cursor killdb_cursor
end
end
else
begin
print ‘ ONLY A SYSTEM ADMINISTRATOR CAN EXECUTE sp__kill_db.’
print ‘ ‘
end
go