Se intampla uneori,  aparent fara motiv, ca utilizatorii sa se planga ca le merg greu aplicatiile, sau ca asteapta mult sa se ruleze ceva in aplicatie.

De multe ori acest lucru se intampla din cauza aparitiei blocajelor pe server.

Pentru aceste situatii am facut o procedura care , la rulare, returneaza toate sesiunile ce genereaza blocaje sau care sunt blocate de alte sesiuni (asta in cazul in care exista blocaje pe server).

In cazul in care sunt blocaje procedura returneaza:

data,

baza pe care este blocajul,

userul si numele statiei care ruleaza sesiunea ce blocheaza,

userul si numele statiei ce ruleaza sesiunea ce este blocata,

scriptul rulat de userul blocat,

scriptul rulat de userul ce blocheaza,

precum si numele aplicatiilor implicate in blocaj (asta daca scripturile sunt rulate in cadrul unei aplicatii, si nu direct din sql).

In cazul in care nu exista blocaje la momentul rularii, procedura nu va intoarce, evident,  decat capul de tabel.

alter procedure [dbo].[BD_Monitorizare_Blocaje2]
as

declare @spid int
declare @blk_by int
declare @spid2 int
declare @buffer1 varchar(8000)
declare @buffer2 varchar(8000)
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
declare @login1 varchar(50)
declare @login2 varchar(50)
declare @database_name varchar(20)
declare @dbid int
declare @sql5 varchar(8000)
declare @sql6 varchar(8000)
declare @hostname1 varchar(100)
declare @hostname2 varchar(100)
declare @program_name1 varchar(100)
declare @program_name2 varchar(100)

create table #BD_Monitorizare_Procese
(
spid varchar(50),
login varchar(100),
hostname varchar(100),
program_name varchar(100),
blk_by varchar(100),
dbid varchar(100),
last_batch datetime
)

insert into #BD_Monitorizare_Procese
select
spid
,convert(sysname, rtrim(loginame))
,hostname
,program_name
,blocked
,dbid
, last_batch
from master.dbo.sysprocesses with (nolock)
where len(hostname)>0

if exists (select top 1 1 from sys.sysobjects where name = ‘BD_Monitorizare_Blocaje_tbl2’)
drop table BD_Monitorizare_Blocaje_tbl2

CREATE TABLE [dbo].[BD_Monitorizare_Blocaje_tbl2](
[data] [datetime] NULL DEFAULT (getdate()),
[database_name] [varchar](20) NULL,
[login1_blocat] [varchar](50) NULL,
[login2_care_blocheaza] [varchar](50) NULL,
[hostname_login1] [varchar](50) NULL,
[hostname_login2] [varchar](50) NULL,
[buffer_login1] [varchar](8000) NULL,
[buffer_login2] [varchar](8000) NULL,
[program_name1] [varchar](100) NULL,
[program_name2] [varchar](100) NULL
) ON [PRIMARY]

DECLARE xcursor CURSOR FOR
SELECT spid FROM #BD_Monitorizare_Procese order by spid
OPEN xcursor
FETCH NEXT FROM xcursor INTO @spid
WHILE (@@fetch_status = 0)
BEGIN
select @blk_by = blk_by from #BD_Monitorizare_Procese where spid=@spid
if @blk_by <> 0
begin

select @login1 = login from #BD_Monitorizare_Procese where spid=@spid
select @login2 = login from #BD_Monitorizare_Procese where spid=@blk_by
select @dbid = dbid from #BD_Monitorizare_Procese where spid=@spid
select @database_name = name from sys.sysdatabases where dbid=@dbid
select @hostname1 = hostname from #BD_Monitorizare_Procese where spid=@spid
select @hostname2 = hostname from #BD_Monitorizare_Procese where spid=@blk_by
select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@spid
select @program_name1 = program_name from #BD_Monitorizare_Procese where spid=@blk_by

create table #dbcc(c1 varchar(20), c2 int, c3 varchar(8000))

insert #dbcc EXEC(‘dbcc inputbuffer(‘+@spid+’)’)
select @buffer1 = c3 from #dbcc

delete from #dbcc

insert #dbcc EXEC(‘dbcc inputbuffer(‘+@blk_by+’)’)
select @buffer2 = c3 from #dbcc

drop table #dbcc

set @buffer1 = replace(@buffer1,””,’`’)
set @buffer2 = replace(@buffer2,””,’`’ )

insert into BD_Monitorizare_Blocaje_tbl2 ( database_name, login1_blocat, login2_care_blocheaza, hostname_login1, hostname_login2, buffer_login1, buffer_login2 )
values (@database_name, @login1, @login2, @hostname1, @hostname2, @buffer1, @buffer2 )

FETCH NEXT FROM xcursor INTO @spid
end
else
FETCH NEXT FROM xcursor INTO @spid

END
CLOSE xcursor
DEALLOCATE xcursor

select * from BD_Monitorizare_Blocaje_tbl2