sqlserver kill block session

原创
2017/08/13 00:41
阅读数 686

SQL server 查找会话

sp_who
sp_who2
use master
GO
select * from sysprocesses (nolock) where blocked = 0 and spid in (
  select blocked from sysprocesses (nolock) where blocked <> 0
)
GO

sql2000 或2005中查找运行时间长的spid 进程

select
    P.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER'

--查看相应的结果
declare
    @spid int
,   @stmt_start int
,   @stmt_end int
,   @sql_handle binary(20)

set @spid = XXX -- Fill this in

select  top 1
    @sql_handle = sql_handle
,   @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
,   @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from    master.dbo.sysprocesses
where   spid = @spid
order by ecid

SELECT
    SUBSTRING(  text,
            COALESCE(NULLIF(@stmt_start, 0), 1),
            CASE @stmt_end
                WHEN -1
                    THEN DATALENGTH(text)
                ELSE
                    (@stmt_end - @stmt_start)
                END
        )
FROM ::fn_get_sql(@sql_handle)

使用DMV查询

SELECT
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
    p.program_name, text ,r.total_elapsed_time,b.blocking_session_id,r.cpu_time
FROM
    sys.dm_exec_requests AS r,
    master.dbo.sysprocesses AS p 
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
    p.status NOT IN ('sleeping', 'background') 
AND r.session_id = p.spid

 

kill 会话

Kill @spid 

使用脚本自动kill

use master
GO
CREATE PROC support_KillBlockingProcesses (@RecursiveCount int = NULL)
AS

DECLARE @count int, @spid int, @sql nvarchar(max)
SET @count = ISNULL(@RecursiveCount, 3)

while @count > 0
begin

 begin try
  set @spid = (
   select top 1 spid from sysprocesses (nolock)
   where blocked = 0 and spid in (
    select blocked from sysprocesses (nolock) where blocked <> 0
   )
  )
  if @spid > 50
  begin
   set @sql = N'kill ' + cast(@spid as nvarchar(100))
   exec sp_executesql @sql
   --print @sql
  end
 end try
 begin catch
  --print 'error'
 end catch

 set @count = @count - 1
end
GO
----------------
--Usage: ListBlocking [@KillOrphanedProcesses = 0] 
--If @KillOrphanedProcesses is set to 1 then the script will attempt to kill orphaned processes.
--https://www.codeproject.com/tips/267037/list-blocking-processes-in-sql-server
------------------
IF NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[ListBlocking]') 
AND type in (N'P', N'PC'))
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[ListBlocking] AS'
GO
 
/*===================================================================
 
Description:    Wrapper to sp_who2 to show only those processes that 
                are blocking. 
                
                Please see http://support.microsoft.com/kb/224453 for
                more info on locking.
 
===================================================================*/ 
 
ALTER procedure ListBlocking
(
    @KillOrphanedProcesses bit = 0
)
as
 
IF OBJECT_ID('tempdb..#Process') IS NOT NULL drop table #Process
IF OBJECT_ID('tempdb..#BlockingProcess') IS NOT NULL drop table #BlockingProcess
 
create table #Process (
    SPID int,
    Status varchar(500),
    Login varchar(500),
    Hostname varchar(500),
    BlkBy varchar(50),
    DBName varchar(500),
    Command varchar(500),
    CPUTime int,
    DiskIO int,
    LastBatch varchar(500),
    ProgramName varchar(500),
    SPID2 int,
    RequestId int
)
 
CREATE TABLE #BlockingProcess (
    BlockingProcessID int IDENTITY(1,1),
    ProcessID varchar(20),
    EventType varchar(100),
    Parameters varchar(100),
    EventInfo varchar(500),
    CPUTime int,
    DiskIO int,
    TransactionCount int
)
 
DECLARE @BlockingPID        varchar(20),
        @CPUTime            int, 
        @DiskIO             int,
        @TransactionCount   int
 
-- Let SQL Server get us a list of what's going on
SET NOCOUNT ON
insert into #Process exec sp_who2
 
-- From this list of what's going on, get those items that are blocked, and loop through them
DECLARE ProcessCursor CURSOR FAST_FORWARD FOR 
    SELECT BlkBy, SUM(CPUTime) as CPUTime, SUM(DiskIO) as DiskIO
    FROM #Process
    WHERE ISNULL(BlkBy,'') <> ''
    GROUP BY BlkBy
OPEN ProcessCursor
 
FETCH NEXT FROM ProcessCursor INTO @BlockingPID, @CPUTime, @DiskIO
WHILE @@FETCH_STATUS = 0
BEGIN
 
    -- Only valid PIDs
    if ISNULL(@BlockingPID, '') <> '' and @BlockingPID <> '0' AND @BlockingPID <> '  .'
    BEGIN
 
        -- For each blocked process get what information we can on the process and what's blocking it
        SET @TransactionCount = 0
 
        --  -2 = The blocking resource is owned by an orphaned distributed transaction.
        IF SUBSTRING(@BlockingPID, 1, 2) = '-2' 
        BEGIN
        
            -- For orphaned processes we need to get the Unit of work if we're to do anything with it
            DECLARE @UnitOfWork varchar(50)
            select top 1 @UnitOfWork = ISNULL(req_transactionUOW, '')
            from master..syslockinfo
            where req_spid = -2
                
            if @KillOrphanedProcesses = 1
            BEGIN
                if @UnitOfWork <> '' exec('KILL ''' + @UnitOfWork + '''')
                
                INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
                VALUES('', '', '- Killed UOW ' + @UnitOfWork + ' -')
            END
            ELSE
            BEGIN
                INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
                VALUES('', '', '- Orphaned. UOW = ' + @UnitOfWork + ' -')
            END
        END
        
        -- -3 = The blocking resource is owned by a deferred recovery transaction.
        ELSE IF SUBSTRING(@BlockingPID, 1, 2) = '-3' 
        BEGIN
            INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
            VALUES('', '', '- deferred recovery transaction -')
        END
        
        -- -4 = Session ID of the blocking latch owner could not be determined at this 
        --      time because of internal latch state transitions.
        ELSE IF SUBSTRING(@BlockingPID, 1, 2) = '-4' 
        BEGIN
            INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
            VALUES('', '', '- Latch owner could not be determined -')
        END
 
        -- Nothing unusual here. A process is being blocked by another processes, so let's get the
        -- info on the process that's doing the blocking
        ELSE
        BEGIN
 
            SELECT @TransactionCount = open_tran FROM master.sys.sysprocesses WHERE SPID=@BlockingPID
 
            INSERT INTO #BlockingProcess (EventType, Parameters, EventInfo)
            EXEC ('DBCC INPUTBUFFER(' + @BlockingPID + ') WITH NO_INFOMSGS')
        END         
        
        -- Add some aggregate info on the blocking processes
        UPDATE #BlockingProcess 
        SET ProcessID        = @BlockingPID,
            CPUTime          = @CPUTime,
            DiskIO           = @DiskIO,
            TransactionCount = @TransactionCount
        WHERE BlockingProcessID = SCOPE_IDENTITY()
    END
    
    FETCH NEXT FROM ProcessCursor INTO @BlockingPID, @CPUTime, @DiskIO
END
 
CLOSE ProcessCursor
DEALLOCATE ProcessCursor
 
-- Display the results
SELECT ProcessID, EventInfo, TransactionCount, CPUTime, DiskIO FROM #BlockingProcess
 
SET NOCOUNT OFF

 

refer

http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部