• Sqlserver 锁表查询代码记录,未释放连接查询


    --方法1
    WITH
    CTE_SID ( BSID, SID, sql_handle ) AS ( SELECT blocking_session_id , session_id , sql_handle FROM sys.dm_exec_requests WHERE blocking_session_id <> 0 UNION ALL SELECT A.blocking_session_id , A.session_id , A.sql_handle FROM sys.dm_exec_requests A JOIN CTE_SID B ON A.SESSION_ID = B.BSID ) SELECT C.BSID , C.SID , S.login_name , S.host_name , S.status , S.cpu_time , S.memory_usage , S.last_request_start_time , S.last_request_end_time , S.logical_reads , S.row_count , q.text FROM CTE_SID C JOIN sys.dm_exec_sessions S ON C.sid = s.session_id CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q ORDER BY sid



    --方法二

    declare @spid int,@bl int,
    
     @intTransactionCountOnEntry  int,
    
            @intRowcount    int,
    
            @intCountProperties   int,
    
            @intCounter    int
    
    
    
     create table #tmp_lock_who (
    
     id int identity(1,1),
    
     spid smallint,
    
     bl smallint)
    
     
    
     IF @@ERROR<>0 print @@ERROR
    
     
    
     insert into #tmp_lock_who(spid,bl) select  0 ,blocked
    
       from (select * from sysprocesses where  blocked>0 ) a 
    
       where not exists(select * from (select * from sysprocesses where  blocked>0 ) b 
    
       where a.blocked=spid)
    
       union select spid,blocked from sysprocesses where  blocked>0
    
    
    
     IF @@ERROR<>0 print @@ERROR 
    
      
    
    -- 找到临时表的记录数
     select  @intCountProperties = Count(*),@intCounter = 1
    
     from #tmp_lock_who
    
     
    
     IF @@ERROR<>0 print @@ERROR 
    
     
    
     if @intCountProperties=0
    
      select N'现在没有阻塞和死锁信息' as message
    
    
    
    -- 循环开始
    while @intCounter <= @intCountProperties
    
    begin
    
    -- 取第一条记录
      select  @spid = spid,@bl = bl
    
      from #tmp_lock_who where Id = @intCounter 
    
     begin
    
      if @spid =0 
    
                select N'引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + N'进程号,其执行的SQL语法如下'
    
     else
    
                select N'进程号SPID:'+ CAST(@spid AS VARCHAR(10))+ N'被进程号SPID:'+ CAST(@bl AS VARCHAR(10)) +N'阻塞,其当前进程执行的SQL语法如下'
    
     DBCC INPUTBUFFER (@bl )
    
     end 
    
    
    
    -- 循环指针下移
     set @intCounter = @intCounter + 1
    
    end
    
    
    
    drop table #tmp_lock_who
     

     以下为未翻译连接查询及说明(说明 内容摘抄于https://www.cnblogs.com/MrHSR/p/9156452.html)

    declare @dbName varchar(32)='master'
     select @dbName,a.spid,a.kpid,a.waittime,a.blocked ,a.status,hostname,[program_name],cmd,[loginame],a.open_tran,b.text,a.last_batch From [dbo].[SYSPROCESSES] a OUTER APPLY sys.dm_exec_sql_text (a.sql_handle) AS b  
       where a.dbid=db_ID(@dbName ) and a.spid<>@@SPID   order by last_batch

    常用字段说明

    字段名称

    说明

    spid 会话ID(进程ID)

    SQL内部对一个连接的编号,一般来讲,小于50,如果用户连接的编号,大于50

    blocked 阻塞ID

    阻塞的进程ID, 值大于0表示阻塞,  值为本身进程ID表示io操作

    如果blocked>0,但waittime时间很短,说明阻塞时间不长,不严重

    waitresource 等待资源

    格式为 fileid:pagenumber:rid 如(5:1:8235440)

    kipid 线程ID

    当kpid值为不0时,代表当前是活动用户

    kpid=0, waittime=0     空闲连接

    kpid>0, waittime=0     运行状态

    kpid>0, waittime>0     需要等待某个资源,才能继续执行,一般会是suspended(等待io)

    kpid=0, waittime=0    但它还是阻塞的源头,查看open_tran>0 事务没有及时提交

    waittime

    当前等待时间(以毫秒为单位)

    open_tran

     进程的打开事务数

    hostname

    建立连接的客户端工作站的名称

    program_name 

    应用程序的名称

    hostprocess

    工作站进程 ID 号

    loginame

     登录名

    status

    running = 会话正在运行一个或多个批
    background = 会话正在运行一个后台任务,例如死锁检测
    rollback = 会话具有正在处理的事务回滚
    pending = 会话正在等待工作线程变为可用
    runnable = 会话中的任务在等待,由scheduler来运行的可执行队列中。(重要)
    spinloop = 会话中的任务正在等待调节锁变为可用。
    suspended = 会话正在等待事件(如 I/O)完成。(重要)
    sleeping = 连接空闲

    如果status 上有好几个runnable状态任务,需要认真对待。 cpu负荷过重没有及时处理用户的并发请求

    last_batch 

    (客户最后一次调用存储过程或者执行查询的时间)

    open_tran
     连接开启的事务数

     

    一次kill 所有指定数据库 已经sleeping的连接

    declare @db varchar(32)='master'
    ---游标更新删除当前数据
    ---1.声明游标
    declare orderNum_03_cursor cursor scroll
    for select a.spid,a.status From [dbo].[SYSPROCESSES] a OUTER APPLY sys.dm_exec_sql_text (a.sql_handle) AS b where a.dbid=db_ID(@db) and a.spid<>@@SPID and a.spid>50 and b.text is not null 
    --2.打开游标
    open orderNum_03_cursor
    --3.声明游标提取数据所要存放的变量
    declare @pid int ,@status varchar(32)
    --4.定位游标到哪一行
    fetch First from orderNum_03_cursor into @pid,@status --into的变量数量必须与游标查询结果集的列数相同
    while @@fetch_status=0 --提取成功,进行下一条数据的提取操作 
    begin
    if(LTRIM(rtrim(@status))='sleeping')
    begin    
    exec('kill '+@pid)
    end
    fetch next from orderNum_03_cursor into @pid,@status --移动游标
    end 
    --关闭游标
    close orderNum_03_cursor
    --释放
    DEALLOCATE orderNum_03_cursor

    其它数据库维护资料

      http://www.maomao365.com/?p=5464 (mssql sqlserver 数据库常用维护脚本收集)

  • 相关阅读:
    Docker之设置加速器
    Nginx之美多商城前台部署
    Nginx之负载均衡
    Nginx之反向代理
    美多商城后台MIS系统部署之Nginx配置
    Nginx之server和location配置使用
    题解 P4721 【【模板】分治 FFT】
    题解 P3338 【[ZJOI2014]力】
    题解 CHSEQ22 【Chef and Favourite Sequence】
    题解 CF813F 【Bipartite Checking】
  • 原文地址:https://www.cnblogs.com/itstac/p/10819865.html
Copyright © 2020-2023  润新知