We help IT Professionals succeed at work.

How can a process block itself?

lippert asked
Medium Priority
Last Modified: 2016-11-17
First off, let me say that I'm a programmer not the DBA so please excuse any obvious stupidity. We have a moderately sized SQL2000 (SP4) environment (120GB) with a dozen databases the largest being about 30GB. All the MDF files are on our on one large SAN connected to the Server (Win2003) using fiber.

We are experiencing some serious performance issues. I don't know if this is related, but I have noticed something I can't explain. When checking for SQL process locks (using SQL Enterprise Manager) I often find something like:

spid 57 (Blocked By 57)
spid 57 (Blocking)

This can happen to almost any process. Almost everything in our environment runs as a stored proc and I know there isn't one or even a handful of procs that can exhibit this problem. Almost any proc appears exihibit this condition at one time or another. Most of our procs are written to run so quickly that a casual observer wouldn't notice the blocking. but it still happens occassionally to most procs.

The only commonality i can find is that all of the offending procs seem to cause some activity in tempdb as tempdb is usually listed in the objects associated with the process. I'm stumped. What the heck is going on here? Could this be because everything is on the SAN and we're I/O bound? Even if that's the case, how can a process block itself?

Watch Question

Hi Lippert,

I've experienced this behaviour at one client where I work who has an almost identical setup.  After discussing this with a Microsoft representative, we were informed that this is not related to locks, but due to the latch waits you see in sysprocesses.  As you have assumed it is IO related - basically it is caused by the spid waiting for a page to load into memory.  The process (very roughly) involves the spid aquiring two latches to the same page, the first to prevent any processes (including itself) from attempting to read the page until it is loaded into memory (and the latch is relased) and the second to read the page once the first is complete.

If this isn't clear enough - or you'd like the full detail - please let me know.


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

If thers a MSDTC involved , it can happen (MS DTC Transaction Commit Operation Blocks Itself)

Check this site http://support.microsoft.com/kb/821740/en-us?spid=2852&sid=global

if this is not the case, i assume ur sql server is sp4

sp3a doesnt report these issues it seems


To:  gauthampj

Per Microsoft:

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4."

We are running SP4.

Hi lippert,

Some further information that is most likely what you are seeing:





To  Hillwaaa:

This sounds quite plausible. I have been experimenting som and have found a proc that is 'borderline'. If I run it "as-is" there is no locks reported, but if I add a very simple where clause, it reports it is blocking itself. Also, if I run it "as-is" and another proc is run (We have a night shift overseas that acces our database via a web application using ADO to call stored procs.) it will start out not reporting a lock, but as soon as the other proc starts, both will report locks. note here that neither proc is accessing the same database, just the same SQL server instance.

So, if you still think that your answer is the right one, I'll take you up on your offer to have you provide full details. (I'd also like to know what your solution was or what you think we need to do to solve our problem.) Send the details and I'll award the points...


Hi Ed

Was the kb article sufficient? The info we got from Microsoft was pretty much a cut and paste job from that.

To confirm that your issue is definately this, when the run the scenario you described with the 'borderline' sp blocking itself, if you look in Enterprise Manager, under Current Activity and Process Info, you should see a wait type of PAGEIOLATCH_XX.  This just means that it is waiting for the information to be loaded from disk.  

In our client case we think that our performance issues are due to poor SAN performance (as running the same load on the server's local drive does not see the same wait times) - and that the blocking we are seeing is due to the additional information that SP4 is providing.

I have a similar situation but in my case the process is locked for an extended period of time, and this process shows information concerning an specific table. Now this table is not the biggest, but whenever we run a query that returns all rows this query might hang for more that half an hour before I just cancel it.
However if I do a query that will likely return newer results the query will respond almost immediately.

Now from what I gathered from Hillwaaa's first post and I quote "The process (very roughly) involves the spid aquiring two latches to the same page, the first to prevent any processes (including itself) from attempting to read the page until it is loaded into memory (and the latch is relased) and the second to read the page once the first is complete."

I'm guessing that at least one of the pages on which some of the data on that table is located in a damaged, too fragmented or bad sector; therefore while the process waits for the page to be loaded into memory it blocks itself but since the page is on a damaged sector is never loads so the process blocks itself for an extended period of time.

Hopefully my situation might be of some help to anyone.
OMG, SQL 2000 SP4 was a complete nightmare, as was upg from 2000 -> 2005, and parameter sniffing issues.  Those days were many late nights and screaming users.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.