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