Post reply

Blocking

  • September 16, 2008 at 5:00 pm

    #70761

    If you can shed some light on what do I need to be digging for to possibly find problem relating to blocked by and blocking.

    Current Scenario

    Under locks/process id

    there is spid 12 (blocked by 12)

    and right below there is spid 12 (blocking)

    I can see on which db this blocking is happenning but what else would you look for to see if there is any possible problem happenning.

    when I go to properties for different types of lock type within that spid, it says

    Last TSQL command batch: sp_unprepare;1

    Thank you in advance for help.

  • September 17, 2008 at 1:32 am

    #872827

    It's not unusual (in SP4 of SQL 2000) to see self-blocking. It's usually nothing to worry about. It may indicate parallelism or just that part of the query execution is waiting for another part to complete.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 17, 2008 at 3:17 am

    #872883

    A SPID blocking itself is actually SQL Server's way of showing that a SPID is waiting for I/O to complete... these never used to be shown in sysprocesses/sp_who2 until SP3 or SP4 (can't remember which one introduced the change).

  • September 17, 2008 at 9:29 am

    #873088

    Thank you for useful information.

Viewing 4 posts - 1 through 3 (of 3 total)

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