Post reply

SPID blocked by itself !

  • January 6, 2007 at 12:28 am

    #174429

    Hi all,

    I am new to SQL server.

    today i found that one SPid was blocking itself in the sysprocesses system table.

    in wht synario it is possible..?

    i m confused...

    thnks a lot for Any Help !

    Thnks & regards,

    Tushar

     

     

     

     

     

     

  • January 6, 2007 at 8:37 am

    #681638

    I can be because of parallel processing/fetching.

  • January 6, 2007 at 12:15 pm

    #681648

    Microsoft defines it latch, a process is waiting for output from IO. In most of cases, the self-blocks can be unlatched. However, there is potential the self-lock will develop to a dead lock.

  • January 8, 2007 at 10:09 am

    #681830

    What was the process doing (Inserst, Updates, Deletes) ? Did any of the target tables have trigger(s) on them ? Were the table(s) being manipulated very large ? Have you been able to reproduce the self blocking ? Have you tried to capture the event(s) in profiler ? The answers to these questions may help ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • January 8, 2007 at 10:58 am

    #681841

    It has never happened to me as far as I'm aware, but Andras (of SQL Refactor fame) investigated a self-deadlock in SQL Server 2005 SP1 and wrote about it on his Blog

    Self deadlocking transactions in SQL Server 2005 SP1

     

    Best wishes,
    Phil Factor

  • January 8, 2007 at 1:16 pm

    #681872

    Service Pack 4 introduced this:

    http://support.microsoft.com/default.aspx/kb/906344

  • January 24, 2007 at 7:56 am

    #685223

    thnks a lot.

     

    I got much info than expected !

Viewing 7 posts - 1 through 6 (of 6 total)

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