Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: Self Blocking SP4

    Anyone out there having "self blocking" issues since upgrading to SP4 on SQL 2000? Enabing trace flag 9059 has not helped, is this a parallelism issue ?

    thanks,
    Paul

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This is just a feature iof SQL SP4. If a spid is waiting on disk IO, it gives populates the blocked column of sysprocesses with itself. There is no way to eliminate it, except by eliminating the IO. The process is not actually blocking itself, but is just giving you more information about itself. I have no idea why they picked the blocked column to do that in, though.

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by MCrowley
    This is just a feature iof SQL SP4. If a spid is waiting on disk IO, it gives populates the blocked column of sysprocesses with itself. There is no way to eliminate it, except by eliminating the IO. The process is not actually blocking itself, but is just giving you more information about itself. I have no idea why they picked the blocked column to do that in, though.
    It slows down the process 10 fold though.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do you have AWE enabled, or the /3GB switch in your boot.ini?

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by MCrowley
    Do you have AWE enabled, or the /3GB switch in your boot.ini?
    Let me check, Infrastructure guys set the box up. It has 3.5 GB RAM. SQL2K SE.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Also, SP4 was less forgiving of joins on different datatypes. I forget the exact details, but examine the slowest queries to make sure that like datatypes are being joined. Not necessarily same length, but the same types.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's one hell of a "service" pack

    I have not applied that one, but I wonder how you could back out a pack
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I believe the readme docs say to uninstall/reinstall, and restore your copies of master and msdb from before the installation.

    Oh, and you are officially out of support, by the way. As of July Microsoft will not officially support SP3a. They will likely give you the "old college try", but they are not really bound to do anything but tell y ou to upgrade.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •