Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Shailesh Patangay » Find Blocked SQL and Blocked By Information.
Add to briefcase

Find Blocked SQL and Blocked By Information. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
shailesh patangay
Posted Wednesday, June 17, 2009 4:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 7:21 PM
Points: 5, Visits: 86
Comments posted to this topic are about the item Find Blocked SQL and Blocked By Information.
Post #737055
RML51
Posted Wednesday, June 24, 2009 12:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 AM
Points: 1,268, Visits: 1,477
Looks useful, but your code as posted does not parse for me.


Post #741277
esteban alvino quispe
Posted Thursday, June 25, 2009 8:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 1:35 PM
Points: 11, Visits: 42
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
Post #741871
passivebyz
Posted Thursday, May 27, 2010 12:56 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 03, 2010 9:50 AM
Points: 82, 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
Post #929231
Abrar Ahmad_
Posted Tuesday, May 31, 2011 4:09 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 5:49 AM
Points: 202, Visits: 1,043
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!!!
Post #1117283
shailesh patangay
Posted Tuesday, May 31, 2011 2:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 19, 2013 7:21 PM
Points: 5, Visits: 86
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
Post #1117745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.