September 16, 2008 at 5:00 pm
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
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
September 17, 2008 at 3:17 am
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
Thank you for useful information.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy