Post reply

Process Blocking Itself

  • December 6, 2005 at 10:35 am

    #110402

    I'm executing the following SQL statement:

    Update AP

    SET AP.AID=A.AID

    From AP

    Join A on AP.CID=A.CID

    There are about 10 million records in the AP table.

    Regardless of whether I execute this statement in DTS or Query Analyzer the process hangs.  I see that the process that's running the statement (#58 in this example) is blocked and the blocking process is also #58.

    How can a process block itself and how do I eliminate the blocking of what appears to be a very simple statement?

    Thanks

  • December 6, 2005 at 2:12 pm

    #608053

    If you are using SP4, take a look http://support.microsoft.com/default.aspx/kb/906344.

     

  • December 6, 2005 at 2:45 pm

    #608062

    Thanks peterhe, this is the exact problem. 

    I suppose because of the size of the table relative to the performance of my server, this issue became apparent - on smaller tables I never saw it.

    I appreciate your help.

  • December 6, 2005 at 7:19 pm

    #608107

    The issue sounds like it's the same, but when I execute the statement, the process hangs like it's been blocked.  For example, currently a 2 million row update has been processing for 2 hours.  It should be 10 minutes.

    Can anyone recommend and troubleshooting that I can perform?

    Thanks.

  • December 7, 2005 at 3:35 am

    #608131

    Do you have any UPDATE triggers between the two tables?

    If it ain't broke, don't fix it...

  • December 7, 2005 at 3:50 pm

    #608294

    No.  Thanks for your help.

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

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