Find Blocked SQL and Blocked By Information.
-
Comments posted to this topic are about the item Find Blocked SQL and Blocked By Information.
-
Looks useful, but your code as posted does not parse for me.
-
yes, same to me, but this is when copy from web page to sql console, it must will work if you put
the script file to download.Thank you
-
Yes, it's not parsing and getting below errors:
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 4
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 25
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 39
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 43
Incorrect syntax near '?'.
Msg 319, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 54
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 58
Must declare the scalar variable "@spid".
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 60
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 62
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 63
Must declare the scalar variable "@spid".
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 65
Must declare the scalar variable "@str".
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 66
Must declare the scalar variable "@blocked".
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 68
Must declare the scalar variable "@str".
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 70
Incorrect syntax near '?'.
Msg 156, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 72
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 74
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 83
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 84
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 85
Must declare the scalar variable "@spid".
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 91
Incorrect syntax near '?'.
Thanks
-
passivebyz (5/27/2010)
Yes, it's not parsing and getting below errors:Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 4
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 25
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 39
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 43
Incorrect syntax near '?'.
Msg 319, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 54
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 58
Must declare the scalar variable "@spid".
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 60
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 62
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 63
Must declare the scalar variable "@spid".
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 65
Must declare the scalar variable "@str".
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 66
Must declare the scalar variable "@blocked".
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 68
Must declare the scalar variable "@str".
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 70
Incorrect syntax near '?'.
Msg 156, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 72
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 74
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 83
Incorrect syntax near '?'.
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 84
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Procedure SP_GetBlockedInfo, Line 85
Must declare the scalar variable "@spid".
Msg 102, Level 15, State 1, Procedure SP_GetBlockedInfo, Line 91
Incorrect syntax near '?'.
Thanks
[font="Verdana"]
Copy from Web and Paste into note pad, you will notice strange characters, replace those with space and copy SSMS. And all fine!!!
[/font]
-
Please find updated script for SQL Server 2005\2008
declare @spid int
declare @blocked int
declare @db_name varchar(500)
declare @program_name varchar(1000)
declare @loginame varchar(500)
declare @login_time datetime
declare @last_batch datetime
declare @str varchar(100)
declare @runtimeinmin int
declare @min-2 int
set @min-2 = 1
-- when testing with query analyzer open the following code.
create table #bufferspid(eventtype varchar(200)
,parameters varchar(200)
,eventinfo varchar(7600))
create table #bufferblocked(eventtype varchar(200)
,parameters varchar(200)
,eventinfo varchar(7600))
create table #blocked(
row_id int identity (1,1)
,spid int
,sqlspid varchar(3000)
,blockedby int
,sqlblockedby varchar(3000)
,servername varchar(200)
,dbname varchar(200)
,programname varchar(200)
,loginame varchar(500)
,login_time datetime
,last_batch datetime
,runtimeinmin int)
IF Exists (select * from master..syscursors with (nolock) where cursor_name = 'cur_blocked_spid')
Begin
close cur_blocked_spid
deallocate cur_blocked_spid
End
declare cur_blocked_spid cursor
read_only
for select spid,blocked,db_name(dbid),program_name,loginame,login_time,last_batch ,datediff(mi,last_batch,getdate()) runtimeinmin
from master.dbo.sysprocesses with (nolock)
where blocked > 0
--and datediff(mi,last_batch,getdate()) >= @min-2
open cur_blocked_spid
fetch next from cur_blocked_spid into @spid,@blocked,@db_name,@program_name,@loginame,@login_time,@last_batch,@runtimeinmin
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
set @str = 'dbcc inputbuffer ('+convert(varchar,@spid)+')'
insert #bufferspid
exec (@str)
set @str = 'dbcc inputbuffer ('+convert(varchar,@blocked)+')'
insert #bufferblocked
exec (@str)
insert into #blocked (spid,sqlspid,blockedby,sqlblockedby,servername,dbname,programname,loginame,login_time,last_batch,runtimeinmin)
select
@spid as spid
,(select eventinfo from #bufferspid) as sqlspid
,@blocked as blockedby
,(select eventinfo from #bufferblocked) as sqlblockedby
,@@servername as servername
,@db_name as dbname
,@program_name as programname
,@loginame as loginame
,@login_time as login_time
,@last_batch as last_batch
,@runtimeinmin as runtimeinmin
truncate table #bufferspid
truncate table #bufferblocked
end
fetch next from cur_blocked_spid into @spid,@blocked,@db_name,@program_name,@loginame,@login_time,@last_batch,@runtimeinmin
end
close cur_blocked_spid
deallocate cur_blocked_spid
select spid,programname,sqlspid,blockedby,sqlblockedby,servername,dbname,loginame
,login_time,last_batch,runtimeinmin from #blocked
where spid != blockedby
select 'Details for Blocked Processes'
select * from master..sysprocesses where spid in (select spid from #blocked where spid != blockedby )
select 'Details for BlockedBy Processes'
select * from master..sysprocesses where spid in (select blockedby from #blocked where spid != blockedby )
DROP TABLE #BUFFERSPID
DROP TABLE #BUFFERBLOCKED
DROP TABLE #BLOCKED
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply