Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Blocking Expand / Collapse
Author
Message
Posted Tuesday, September 16, 2008 5:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 2, 2009 12:14 PM
Points: 14, Visits: 113
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.

Post #570685
Posted Wednesday, September 17, 2008 1:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 40,427, Visits: 36,877
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 2008, MVP
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

Post #570786
Posted Wednesday, September 17, 2008 3:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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).


Post #570853
Posted Wednesday, September 17, 2008 9:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 2, 2009 12:14 PM
Points: 14, Visits: 113
Thank you for useful information.
Post #571104
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse