-- ***************************************************************************
-- Copyright (C) PortalSQL 2006
--
-- Fichero: CazaBloqueadores.sql
-- Descr.: Busca bloqueos que se mantengan
-- durante el tiempo que le pasemos por parámetro, si hay bloqueos que se mantienen
-- muestra información relevante
-- autor: Miguel Egea Gómez -- Solid Quality Learning IberoAmericana
--
-- comentarios: Puede ser adecuado meter ejecutar este procedimiento desde un job y
-- usar DatabaseMail para reportar sus resultados al administrador de base
-- de datos.
-- He usado la vista de compatibilidad sys.sysprocesses, esto es porque he c
-- comprobado que algunas ocasiones la tabla sys.dm_exec_request no tiene registros
-- para los bloqueadores.
--
-- Parámetros de entrada:
-- @waittime --> Tiempo que debe esperarse para considerar que hay un bloqueo
-- Valores de retorno : 0--> Funcionó de forma correcta
-- 1--> En cualquir otro caso
--
-- <!--(más reciente más arriba) -->
-- yyyy/mm/dd by description
-- ========== ======= ==========================================================
-- 2006/06/04 MEG Creación.
--
-- ***************************************************************************
-- Ejemplo de llamada
--
/*
DECLARE @Valor varchar(10) ,@result int
SET @Valor='00:00:02'
EXEC @result =CazaBloqueadores @valor
select @result</code>
 
*/ 
 
CREATE PROC CazaBloqueadores @waittime varchar(10)
as
begin
begin try
declare @t table (bloqueado int, bloqueador int)
-- usaremos la variable de tipo tabla para esperar
insert into @t (bloqueado,bloqueador)
select session_id, blocking_session_id
from sys.dm_exec_requests der
where blocking_session_id!=0
-- Recogemos los objetos que están bloqueados..
waitfor delay @waittime
SELECT der.session_id [Sesion bloqueada],
det.text [Comando Bloqueado],
der.[blocking_Session_id] [id del bloqueador],
subq.TEXT [Comando Bloqueador],
des.[host_name] [Nombre del host],
des.[Program_name] [Aplicación],
db_name(der.database_id) [Base de Datos],
user_name(der.user_id) [usuario de bbdd],
des.login_name [login],
cast(des.context_info as varchar(100)) [Informacion Adicional],
des.cpu_time [Tiempo de CPU],
des.total_elapsed_time [Tiempo de espera],
des.logical_reads [lecturas],
des.writes [escrituras],
der.start_time [Comenzó],
der.status [estado],
case des.transaction_isolation_level
when 0 then 'Sin especificar'
when 1 then 'Read Uncommitted'
when 2 then 'Read Committed'
when 3 then 'Repeatable reads'
when 4 then 'Serializable'
when 5 then 'Snapshot'
else 'Inesperado'
end [Nivel de Aislamiento]
FROM sys.dm_exec_requests der
INNER JOIN sys.dm_exec_sessions des
ON der.session_id=des.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) det
left join (select SPID session_id, det2.[text]
from sys.sysprocesses der2
CROSS APPLY sys.dm_exec_sql_text(sql_handle) det2
) subq on der.[blocking_Session_id]=subq.session_id
WHERE der.blocking_session_id
in (select bloqueador from @t)
return 0
end try
begin catch
declare @error sysname
set @error=ERROR_MESSAGE()
raiserror (@error,16,1)
return 1
end catch
end