Friday, 1 March 2013

Kill The Sessions


Declare @DatabaseName Varchar(100)
Set @DatabaseName='TEST_DB' -- Put the database name here


----- Show Data
select 'BEFORE -->',spid,
HOSTNAME,
Loginame,
DB_NAME(dbid) AS Database_Name,
USER_NAME(uid) AS UserName,
Status,
[program_name]
CMD from master.sys.sysprocesses
Where dbid=DB_ID(@DatabaseName)
order by Status,DB_NAME(dbid) desc,Hostname,spid,uid
------

Declare @SessionInfo Table (id int identity(1,1),Session_ID varchar(10))
Declare @count int
Declare @max int
declare @SqlString nvarchar(50)
select @count=1,@max=0

--insert into @SessionInfo(Session_ID)
select spid
from master.sys.sysprocesses
Where dbid=DB_ID(@DatabaseName)
and Status<>'runnable'
return

select @max=MAX(id) from @SessionInfo

while (@count<=@max)
BEGIN
    set @SqlString=''
    select @SqlString='KILL '+Session_ID from @SessionInfo where id=@count
    EXEC sp_executesql @SqlString
Set @count=@count+1;
END

select 'AFTER -->',spid,
HOSTNAME,
Loginame,
DB_NAME(dbid) AS Database_Name,
USER_NAME(uid) AS UserName,
Status,
[program_name]
CMD from master.sys.sysprocesses
Where DB_NAME(dbid)=@DatabaseName
order by Status,DB_NAME(dbid) desc,Hostname,spid,uid

No comments:

Post a Comment