SP para analizar bloqueos en la BBDD, crear este SP en Master.
Para llamar al SP «sp_who2 active», podemos revisar los bloqueos ejecutando «dbcc inputbuffer(SPID)»
USE [master]
GO
/****** Object: StoredProcedure [sys].[sp_who2] Script Date: 11/21/2017 09:21:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [sys].[sp_who2] --- 1995/11/03 10:16
@loginame sysname = NULL
as
set nocount on
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
-- defaults
select @retcode = 0 -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from sys.syslogins where loginname = @loginame)
select @sid1 = sid from sys.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame collate Latin1_General_CI_AS)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
raiserror(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
select
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
,request_id
into #tb1_sysprocesses
from sys.sysprocesses_ex with (nolock)
if @@error <> 0
begin
select @retcode = @@error
GOTO LABEL_86RETURN
end
--------Screen out any rows?
if (@loginame in ('active'))
delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) in (
'AWAITING COMMAND'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
select
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh
--------Output the report.
EXEC(
'
SET nocount off
SELECT
SPID = convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.
,REQUESTID = convert(char(5),request_id)
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
-- (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
return @retcode -- sp_who2
SP_WHO3, igual que sp_who2, pero con detalle de querrys y mas info (añadir el SP a programability en la BBDD master) :
USE [Master]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 11/21/2017 09:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_who3] (@loginame Sysname = NULL)
As
Set NoCount On
-- Variables
Declare @retcode Int
Declare @spidlow Int,
@spidhigh Int
Declare @charMaxLenLoginName Varchar(6),
@charMaxLenDBName Varchar(6),
@charMaxLenCPUTime Varchar(10),
@charMaxLenDiskIO Varchar(10),
@charMaxLenHostName Varchar(10),
@charMaxLenProgramName Varchar(10),
@charMaxLenLastBatch Varchar(10),
@charMaxLenLastBatchDuration Varchar(10),
@charMaxLenCommand Varchar(10)
Declare @charsidlow Varchar(85),
@charsidhigh Varchar(85),
@charspidlow Varchar(11),
@charspidhigh Varchar(11)
-- Valores default
Select @retcode = 0 -- 0=good ,1=bad.
Select @spidlow = 0,
@spidhigh = 32767
/* Parte donde se analiza la entrada y se acomodan los parametros */
-- Caso default, no recibi parametro @loginame
If (@loginame Is NULL)
Goto Label_Main
-- Caso en que lo que recibi es un loginname
If Exists(Select * From sys.syslogins Where loginname = @loginame)
Goto Label_Main
-- En caso de recibir el parametro en 'activo' le acomodo el collation
If (Lower(@loginame Collate Latin1_General_CI_AS) In ('active')) --Special action, not sleeping.
Begin
Select @loginame = Lower(@loginame Collate Latin1_General_CI_AS)
Goto Label_Main
End
-- Caso en que lo que recibi es un numero de pid
If (patindex ('%[^0-9]%' , isNull(@loginame,'z')) = 0)
Begin
Select @spidlow = Convert(Int, @loginame),
@spidhigh = Convert(Int, @loginame)
Goto Label_Main
End
-- Si llegue hasta aca es porque hubo algun error
RaisError(15007, -1, -1, @loginame)
Select @retcode = 1
Goto Label_End
/* Parte principal donde se obtienen y acomodan los datos de los procesos */
Label_Main:
-- Creo la tabla temporal donde voy a guardar los procesos
Create Table #TempProcesos (
spid Smallint,
status NChar(30),
sid Binary(86),
hostname NChar(128),
program_name NChar(128),
cmd NChar(16),
cpu Int,
physical_io Bigint,
blocked Smallint,
dbid Smallint,
loginname Sysname,
spid_sort Smallint,
last_batch_char Varchar(50),
last_batch_duration Varchar(50),
request_id Int,
running_query Varchar(Max),
blocking_query Varchar(Max)
)
-- Meto en la tabla los procesos consultando la sysprocesses
Insert Into #TempProcesos (
spid,
status,
sid,
hostname,
program_name,
cmd,
cpu,
physical_io,
blocked,
dbid,
loginname,
spid_sort,
last_batch_char,
last_batch_duration,
request_id
)
Select
spid,
status,
sid,
hostname,
program_name,
cmd,
cpu,
physical_io,
blocked,
dbid,
Convert(sysname, RTrim(loginame)),
spid,
SubString(Convert(Varchar, last_batch, 111), 6, 5) + ' ' + SubString(Convert(Varchar, last_batch, 113), 13, 8),
Convert(Varchar(8), DateAdd(Second, DateDiff(Second, last_batch, GetDate()), 0), 114),
request_id
From sys.sysprocesses (Nolock)
-- Si hay errores no sigo
If @@error <> 0
Begin
Select @retcode = @@error
Goto Label_End
End
-- Si el parametro recibido es 'active' borro de la tabla temporal a todos los que esten inactivos
If (@loginame In ('active'))
Delete #TempProcesos
Where Lower(status) = 'sleeping'
And Upper(cmd) In ('AWAITING COMMAND', 'LAZY WRITER', 'CHECKPOINT SLEEP')
And blocked = 0
-- Obtengo las querys usando un cursor que recorre todos los procesos y ejecuta un dbcc inputbuffer
Declare @CurrentSpid Int,
@GetQuery NVarchar(Max)
Create Table #TempQuerys (
spid Int,
EventType nvarchar(30),
Parameters int,
EventInfo nvarchar(Max)
)
Declare #CursorProcesos Cursor For
Select spid
From #TempProcesos (Nolock)
Open #CursorProcesos
Fetch Next From #CursorProcesos
Into @CurrentSpid
While @@FETCH_STATUS = 0
Begin
-- Agrego bloque try/catch porque puede no existir el PID si justo se termino la ejeucion
Begin Try
-- Ejecuto un dbcc inputbuffer para obtener la query asociada al spid
Set @GetQuery = N'DBCC INPUTBUFFER(' + Convert(Varchar(255), @CurrentSpid) +') WITH NO_INFOMSGS'
Insert Into #TempQuerys (EventType, Parameters, EventInfo)
Exec sp_ExecuteSql @GetQuery
Update #TempQuerys
Set spid = @CurrentSpid
Where spid Is NULL
End Try
Begin Catch
-- Si hubo algun error borro el registro que puede habe quedado a medio insertar
Delete From #TempQuerys
Where spid Is NULL
End Catch
Fetch Next From #CursorProcesos
Into @CurrentSpid
End
Close #CursorProcesos
Deallocate #CursorProcesos
-- Agrego las querys asociadas a cada PID, en el caso que tengan
Update #TempProcesos
Set running_query = Q.EventInfo
From #TempProcesos P (Nolock)
Join #TempQuerys Q (Nolock)
On P.spid = Q.spid
And Q.EventInfo Is Not NULL
-- Agrego las querys asociadas a los procesos que bloquean, en el caso que existan
Update #TempProcesos
Set blocking_query = Q.EventInfo
From #TempProcesos P (Nolock)
Join #TempQuerys Q (Nolock)
On P.blocked = Q.spid
And P.blocked Is Not NULL
And Q.EventInfo Is Not NULL
-- Convierto los maximo y minimo spid a varchar para usarlo en le query
Select @charspidlow = Convert(Varchar, @spidlow),
@charspidhigh = Convert(Varchar, @spidhigh)
-- Obtengo los maximos de cada campo para ajustar el tamaño de las columnas
Select
@charMaxLenLoginName =
Convert(Varchar
,isNull(Max(Datalength(loginname)), 5)
),
@charMaxLenDBName =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), db_name(dbid))))), 6)
),
@charMaxLenCPUTime =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), cpu)))), 7)
),
@charMaxLenDiskIO =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), physical_io)))), 6)
),
@charMaxLenCommand =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), cmd)))), 7)
),
@charMaxLenHostName =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), hostname)))), 8)
),
@charMaxLenProgramName =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), program_name)))), 11)
),
@charMaxLenLastBatch =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), last_batch_char)))), 9)
),
@charMaxLenLastBatchDuration =
Convert(Varchar
,isNull(Max(Datalength(RTrim(Convert(Varchar(128), last_batch_duration)))), 6)
)
From #TempProcesos (Nolock)
Where spid >= @spidlow
And spid <= @spidhigh
/* Parte donde armo la query completa de salida y la ejecuto */
Exec('
Set NoCount On
Select
SPID = Convert(Char(5),spid),
Status = Case lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
End,
Login = SubString(loginname,1,' + @charMaxLenLoginName + '),
HostName = Case hostname
When NULL Then '' .''
When '' '' Then '' .''
Else SubString(hostname,1,' + @charMaxLenHostName + ')
End,
BlkBy = Case isNull(Convert(Char(5),blocked),''0'')
When ''0'' Then '' .''
Else isNull(Convert(Char(5),blocked),''0'')
End,
DBName = SubString(
Case
When dbid = 0 Then NULL
When dbid <> 0 Then db_name(dbid)
End,
1,' + @charMaxLenDBName + '),
Command = SubString(cmd,1,' + @charMaxLenCommand + '),
CPUTime = SubString(Convert(varchar,cpu),1,' + @charMaxLenCPUTime + '),
DiskIO = SubString(Convert(varchar,physical_io),1,' + @charMaxLenDiskIO + '),
LastBatch = SubString(last_batch_char,1,' + @charMaxLenLastBatch + '),
LastBatchDuration = SubString(last_batch_duration,1,' + @charMaxLenLastBatchDuration + '),
ProgramName = SubString(program_name,1,' + @charMaxLenProgramName + '),
RunningQuery = running_query,
BlockingQuery = blocking_query
From #TempProcesos
Where spid >= ' + @charspidlow + '
And spid <= ' + @charspidhigh + '
Order By Convert(Int, spid)
Set NoCount Off
')
Label_End:
-- Al final destruyo la tablas temporales si existen
If (Object_id('tempdb..#TempQuerys') Is Not NULL)
Drop Table #TempQuerys
If (Object_id('tempdb..#TempProcesos') Is Not NULL)
Drop Table #TempProcesos
Set NoCount Off
Return @retcode
