文档章节

sqlserver kill block session

易野
 易野
发布于 2017/08/13 00:41
字数 846
阅读 14
收藏 0

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

© 著作权归作者所有

共有 人打赏支持
上一篇: pg GUI Tools
下一篇: sqlserver 体系结构
易野
粉丝 5
博文 168
码字总数 125684
作品 0
深圳
私信 提问
Session服务器配置指南与使用经验

一.摘要 所有Web程序都会使用Session保存数据. 使用独立的Session服务器可以解决负载均衡场景中的Session共享问题.本文介绍.NET平台下建立Session服务器的几种办法, 并介绍在使用Session时的...

KavenSu
2014/10/11
0
0
SQLserver 关键字作列名

在用 Informatica 从 SQLserver 中抽取数据时碰到这样一个错误: 实际上,这是一个非常简单的 mapping,从源表直抽数据至目标表,没有转换逻辑: 从 Monitor 的 session log 中获取详细的 in...

DreamOver
06/07
0
0
数据库实现多站点共享Session

数据库实现多站点共享Session 多站点共享Session有很多方法,多站点共享Session常见的做法有: 使用.net自动的状态服务(Asp.net State Service); 使用.net的Session数据库; 使用Redis等缓...

章为忠
2015/08/06
0
0
Session的SqlServer模式的配置

  很多时候,由于各种莫名其妙的原因,会导致session丢失。不过ASP.NET还允许将会话数据存储到一个数据库服务器中,方法是将mode属性变成SqlServer。 在这种情况下,ASP.NET尝试将会话数据...

章为忠
2015/08/05
0
0
【转】Hibernate常见错误

no bean specialed. 出错了,jsp页面报错。 原因是html:select 标签 中 option bean is null/. Set不能加同一实体 在保存数据的时候循环添加一PO数据到Set,居然最后Set的size()为1。各实体设...

mj4738
2012/01/15
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Jrebel 激活服务,在springboot上面的进行热部署

1.安装JRebel 下载Jrebel插件,官网需要翻墙下载,需要的可以在csdn的下载区去进行下载 打开idea,File->settings 然后重启idea 2.破解JRebel 首先HELP -> JRebel -> Activation 在jrebel se...

glen_xu
50分钟前
1
0
设置版头的图片+网页布局

1.div的background-image(推荐) 2.div+image 1.是只有部分图,2是压图 1.frame 2.js(推荐) 因为frame不好设置大小

木之下
54分钟前
0
0
MyBatis组件之缓存实现及使用

一 .概述 先讲缓存实现,主要是mybatis一级缓存,二级缓存及缓存使用后续补充 Mybatis缓存的实现是基于Map的,从缓存里面读写数据是缓存模块的核心基础功能; 除核心功能之外,有很多额外的附...

Ala6
今天
1
0
SpringBoot中使用@RequestBody时如何自定义需要转换的日期格式

SpringBoot序列化和反序列化Json时默认使用的是Jackson(例如使用@RequestBody反序列化前端传递过来的Json字符串时), 当我们前端使用Json字符串传递到后台时日期格式可能是时间戳(即long类...

帅得拖网速
今天
1
0
可自定义扩展底部列表对话框ListBottomSheetDialogFragment

因为需要,为了方便,构建了一个可以自定义扩展的底部列表对话框,可以应付大部分场景。 效果图如下: 1.默认实现: 2.自定义列表实现 3.自定义头部和列表实现 一.可实现功能 1.默认可实现通...

明月春秋
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部