Find Blocked SQL and Blocked By Information.

  • shailesh patangay

    SSC Rookie

    Points: 43

    Comments posted to this topic are about the item Find Blocked SQL and Blocked By Information.

  • RML51

    SSCarpal Tunnel

    Points: 4410

    Looks useful, but your code as posted does not parse for me.

  • esteban alvino quispe

    SSC Rookie

    Points: 39

    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

  • passivebyz

    SSCommitted

    Points: 1908

    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_

    SSCarpal Tunnel

    Points: 4222

    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]

  • shailesh patangay

    SSC Rookie

    Points: 43

    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