文档章节

sqlserver kill block session

易野
 易野
发布于 2017/08/13 00:41
字数 846
阅读 12
收藏 0
点赞 0
评论 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

© 著作权归作者所有

共有 人打赏支持
易野
粉丝 2
博文 142
码字总数 114058
作品 0
深圳
SQLserver 关键字作列名

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

DreamOver ⋅ 06/07 ⋅ 0

SQLServer 开启远程访问,也可逆向思维进行关闭

为了可以通过TCP/IP协议远程访问SQLServer数据库,需要做以下几点: 在SQLServer所运行的服务器上,我们必须找到SQLServer所侦听的端口然后添加到WIndows防火墙的【允许入站】中。 一:需要添...

easonjim ⋅ 2015/08/25 ⋅ 0

MSSQL-Server On Docker

安装先决条件: • 适用于支持的任一 Linux 分发版的 Docker 引擎 1.8 以上版本,或适用于 Mac/Windows 的 Docker。 有关详细信息,请参阅 Install Docker(安装 Docker)。 • 至少 2 GB 的...

jwenshan ⋅ 05/30 ⋅ 0

sqlserver,你的delete语句表名为什么不能别名?

前几天写了几行sql,用于清除环境里面的脏数据,平时开发用的是oracle数据库,看了下里面的语句,处理concat字符串拼接函数看起来稍微特殊点,其它都没有啥问题。百度了下,cancat可以用在收...

heshifk ⋅ 04/23 ⋅ 0

SQL 2017 SQLPS执行Ad-SqlAvailabilityDatabase异常

SQL Server 2017 SQLPS执行Ad-SqlAvailabilityDatabase遇到问题 这个错误简直逆天,查了下该cmdlet的帮助 常规参数里有Debug。 执行Debug,输出如下: PS C:Windowssystem32> Add-SqlAvailab...

UltraSQL ⋅ 05/23 ⋅ 0

SQL Server on Linux 2017(初识 LINUX下的SQL-SERVER功能应用)

安装环境Centos7: 官方安装必要條件: 必须 RHEL 7.3 或 7.4 机至少 2 GB的内存 1、设置mssql_server的YUM官方源: curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsof...

jwenshan ⋅ 05/28 ⋅ 0

“SQLServerAgent当前未运行”问题解决

在执行SQLServer计划任务的时候,出现了如下所示的错误: 解决方法: 配置工具--sqlserver 配置管理器--SQLSERVER服务--右侧最下面--点击启动AGENT即可 ==>如有问题,请联系我:easonjim#16...

easonjim ⋅ 2016/04/08 ⋅ 0

使用 Informatica 做将字符串转换成日期

数据源为 SQLServer 数据库,且源字段 varchar 类型。需要抽取到 Oracle 数据库中,并且转换为 date 类型。 由于源表和目标表是两种不同的数据库,且数据类型不同,所以无法在源表限定转换器...

DreamOver ⋅ 06/08 ⋅ 0

Mybatis 分页插件 PageHelper 5.1.4 发布

如果你也在用 Mybatis,建议尝试该分页插件,这一定是最方便使用的分页插件。 该插件目前支持以下数据库的物理分页 Oracle Mysql MariaDB SQLite Hsqldb PostgreSQL DB2 SqlServer(2005+) I...

Liuzh_533 ⋅ 04/23 ⋅ 0

Sequelize 5.0.0-beta.7 发布,基于 Nodejs 的 ORM 框架

Sequelize 5.0.0-beta.7 发布了,此次更新内容如下: fix(data-types/blob): only return null for mysql binary null #9441 fix(errors): use standard .original rather than .raw for ac......

雨田桑 ⋅ 05/29 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

MySQL主从复制原理、半同步操作步骤及原理

1.1 企业Linux运维场景数据同步方案 1.1.1 文件级别的异机同步方案 1、scp/sftp/nc 命令可以实现远程数据同步。 2、搭建ftp/http/svn/nfs 服务器,然后在客户端上也可以把数据同步到服务器。...

xiaomin0322 ⋅ 20分钟前 ⋅ 0

Oracle10g 数据及文件迁移过程[原]

QL*Plus: Release 10.2.0.1.0 - Production on 星期三 5月 11 10:22:35 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Re......

harrypotter ⋅ 26分钟前 ⋅ 0

nginx安装

1:安装工具包 wget、vim和gcc yum install -y wget yum install -y vim-enhanced yum install -y make cmake gcc gcc-c++ 2:下载nginx安装包 wget http://nginx.org/download/nginx-1......

壹丶贰 ⋅ 29分钟前 ⋅ 0

ideaVim安装及配置

1.安装插件 File-Settings-Plugins,Browse Repositories,输入ideavim,安装。 重启后,在Tools-Vim Emulator启用。 2.快捷键设置 ideaViim键与idea快捷键有冲突,可以在Settings-Other Se...

Funcy1122 ⋅ 33分钟前 ⋅ 0

MySQL中B+Tree索引原理

B+树索引是B+树在数据库中的一种实现,是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡(balance),而不是二叉(binary),因为B+树是从最早的平衡二叉树演化而来的。在讲B...

浮躁的码农 ⋅ 48分钟前 ⋅ 0

两道面试题,带你解析Java类加载机制

在许多Java面试中,我们经常会看到关于Java类加载机制的考察,例如下面这道题: class Grandpa{ static { System.out.println("爷爷在静态代码块"); }} cl...

1527 ⋅ 52分钟前 ⋅ 0

SpringCloud(Data Flow)

dataflow-server

赵-猛 ⋅ 今天 ⋅ 0

深入理解Java虚拟机

这本书我读到第8章,之后就是在读不下去了。 读到后面是一种痛苦的体验,太多的东西是不全面的,大量的专有名词是没有解释的,读到最后很多东西仅仅是一个侧面,所以我觉得,这本书不适合初学...

颖伙虫 ⋅ 今天 ⋅ 0

NanoPi NEO core/ Ubuntu16.04单网卡配置3个IP地址(2个静态,1个动态)

配置 root@NanoPi-NEO-Core:/etc/network# cat interfacesauto loiface lo inet loopbackallow-hotplug eth0iface eth0 inet static address 172.31.188.249 netmask 255.......

SamXIAO ⋅ 今天 ⋅ 0

三步为你的App集成LivePhoto功能

摘要:LivePhoto是iOS9新推出的一种拍照方式,类似于拍摄Gif图或录制视频片段生成图片。如果没有画面感,可以联想《哈利波特》霍格沃茨城堡的壁画,哈哈,很炫酷有木有,但坑爹的是只有iphone6S以...

壹峰 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部