Blocked and Blocking process Help

  • Good afternoon,

    I work in a production system, i'm in charge of monitoring a bunch of sqlprocess in sqlserver 200 server in order to take care of blocked or blocking processes in a level of tables, databse, resources, etc.

    i simple command but useful is

    sp_ active

    and then type dbcc inputbuffer(id_process)

    Is the any other "magic" and useful commands in sqlserver thet tells me which processes are blocking and which ones are being blocked, and which ones are clutering the server resources.

    I'd apprciate your help

  • I don't know what sp_active does since it is a custom stored procedure, but you could look at sp_lock.

  • You can use the "sp_who2 Active" you can find there what is blocked spid and block by spid.

    Cheers!

    dev1.bohol

  • use sp_who2

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • sp who2?? what is it i didn't understand??

    Check it out and get tubing tubes

  • 'sp_who2' It is a stored procedure which is installed with SQL Server. When you execute it, you may able to see the results of blocking processes, SPID's users, hostname, command, status and etc...

    Each row contains a number of useful columns like what i mentioned above. you can also view what its resource usage is in terms of CPU/IO and what login is currently executing the command.

    try to execute this:

    sp_who2 Active & sp_who2

    Cheers!

    -dev1.bohol

  • Oh i see thanks now i understand what it is.. Thankyou:-)

    Check it out and get tubing tubes

  • You are welcome my friend 🙂

    Cheers!

    -dev1.bohol@gmail.com

  • DECLARE @cmdVARCHAR(100),

    @spidVARCHAR(4),

    @SQLVARCHAR(100),

    @timeDATETIME,

    @loginnameVARCHAR(100)

    if OBJECT_ID('TEMPDB..#TempConn2') is not null

    DROP TABLE #TempConn2

    create table #TempConn2

    (EventType nvarchar(30), Parameters Int,EventInfo nvarchar(4000),SPID INT NULL,loginname VARCHAR(100)NULL)

    --blocking

    DECLARE my_cursor CURSOR FOR

    SELECT [SPID],loginame

    FROM sys.sysprocesses

    where blocked<>0

    OPEN my_cursor

    FETCH NEXT FROM my_cursor

    INTO @spid,@loginname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    begin

    set @SQL = 'dbcc inputbuffer (' + @spid + ')'

    insert into #TempConn2 (EventType ,Parameters,EventInfo)

    execute(@SQL)

    UPDATE #TempConn2 SET SPID=@spid,loginname=@loginname where SPID is NULL

    end

    FETCH NEXT FROM my_cursor

    INTO @spid,@loginname

    END

    CLOSE my_cursor

    DEALLOCATE my_cursor

    select * from #TempConn2

    --where eventinfo like '%text%'

    GO this is what i tend run for blocking

  • Hi,

    The script T-SQL you privided is good, but you can just use the dbcc inputbuffer(SPID#) only once you have identified the blocked process and blocked by process.

    By running the command i mentioned, you may able to see what particular T-SQL that is about to run or running.

    Cheers!

    dev1.bohol

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply