SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find Blocked SQL and Blocked By Information.


Find Blocked SQL and Blocked By Information.

Author
Message
shailesh patangay
shailesh patangay
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 149
Comments posted to this topic are about the item Find Blocked SQL and Blocked By Information.
RML51
RML51
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2499 Visits: 1612
Looks useful, but your code as posted does not parse for me.



esteban alvino quispe
esteban alvino quispe
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 42
yes, same to me, but this is when copy from web page to sql console, it must will work if you put :-D the script file to download.

Thank you
passivebyz
passivebyz
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 156
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
Abrar Ahmad_
Abrar Ahmad_
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1312
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


Copy from Web and Paste into note pad, you will notice strange characters, replace those with space and copy SSMS. And all fine!!!

shailesh patangay
shailesh patangay
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 149
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 int
set @min = 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
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search