usp_blocker

最新推荐文章于 2024-06-08 12:22:27 发布

xiulamimi

最新推荐文章于 2024-06-08 12:22:27 发布

阅读量275

点赞数

文章标签： sqlserver SQLServer

USE [CRM]
GO
/****** 对象: StoredProcedure [dbo].[usp_blocker]    脚本日期: 11/24/2012 13:53:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[usp_blocker]

as

begin

set nocount on

declare @spid smallint

declare @c_SQL nvarchar(3000)

declare @id int

--保存 Sysprocesses 的内容

create table #Temp(spid smallint,status nchar(30),hostname nchar(128),program_name nchar(128)

   ,cmd nchar(16),cpu int,physical_io int,blocked smallint,dbid smallint

   ,loginame nchar(128),last_batch datetime

   ,SQLBuffer nvarchar(3000))

--保存 DBCC InputBuffer 的结果

create table #Temp1(id int identity(1,1),eventtype varchar(20),parameters int,eventinfo nvarchar(3000))

select * into #Temp2

from master..sysprocesses (nolock)

--保存被阻塞的进程信息

insert into #Temp(spid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

   ,loginame ,last_batch )

SELECT spid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

     ,convert(sysname, rtrim(loginame)) ,last_batch

   from #Temp2

   where blocked > 0
--保存阻塞的源头

insert into #Temp(spid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

   ,loginame ,last_batch )

SELECT spid ,status ,hostname ,program_name ,cmd ,cpu ,physical_io ,blocked ,dbid

     ,convert(sysname, rtrim(loginame)) ,last_batch

   from #Temp2

   where spid in (select blocked from #Temp)

    and spid not in (select spid from #Temp)

select count(#Temp2.spid) '当前数据库连接数'

from #Temp2

select count(#Temp.spid) '存在阻塞的连接数'

from #Temp

select @spid = min(spid) from #Temp

while @spid is not null

begin

set @c_SQL = 'dbcc inputbuffer(' + convert(varchar(5), @spid) + ')'

insert into #Temp1

   exec (@c_SQL)

select @id = @@identity
   update #Temp
    set SQLBuffer = #Temp1.eventinfo

   from #Temp,#Temp1

   where #Temp1.id = @id

    and #Temp.spid = @spid

select @spid = min(spid) from #Temp where spid > @spid

end

SELECT convert(char(5),spid) SPID,CASE lower(status) When 'sleeping' Then lower(status) Else upper(status) END Status

   ,SQLBuffer
   , CASE hostname When Null Then ' .' When ' ' Then ' .' Else hostname END HostName

   ,CASE isnull(convert(char(5),blocked),'0') When '0' Then ' .'

                       Else isnull(convert(char(5),blocked),'0') END BlkBy

   ,loginame Login

   ,db_name(dbid) DBName,convert(varchar,cpu) CPUTime

   ,convert(varchar,physical_io) DiskIO,Last_Batch LastBatch

   ,program_name ProgramName, cmd Command

from #Temp
order by BlkBy, spid
set nocount off
end