Post reply

Brain numbing page lock problem

  • July 4, 2002 at 2:51 am

    #58282

    This has been driving me up the wall for some days now.

    I have a M$ SQL 2000 clustered box, 4 chips, loads of RAM and free space.

    I have a central table containing market data for UK Companies (3.1 mill), linked to Companies House Data (1.6 mill) and Contacts (3.9 mill) + a few other big resources.

    My problem is this, every update to the Company data table fails. It isn't blocked by any other transaction, but in the current activity window it is shown as waiting for resource PAGEIOLATCH_SH. This resource never becomes free (well I say never, 73 hours waiting for a single row update and you give up ).

    This also happens in any locking operation (ie any writable read). I have copied the entire table with (nolock) to a new table and the new table has the same symptoms.

    I have detached the entire database (all 40 gig of it) and reattached so as only I could access it. Taken the cluster to pieces, rebooted or restarted everything that could be, ran every DBCC process and found no errors.

    Any suggestions before I switch to oracle?

    Edited by - KHENRY on 07/04/2002 02:52:10 AM

  • July 4, 2002 at 5:09 am

    #432109

    Dont know that these will help, but I'll ask anyway?

    1) Table is indexed, you've checked the query plan to see that it makes sense?

    2) Updated statistics?

    3) Double checked what any triggers on the table are doing (and maybe disabled them?)?

    4) Profiled to verify what update is being received on the server?

    Andy

    http://qa.sqlservercentral.com/columnists/awarren/

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • July 4, 2002 at 7:24 am

    #432115

    Thanks for the response, but:

    1) a)Indexed on key I'm using to find row

    b)Optimised query plan

    2)Yup

    3)No triggers

    4)Yes

    I'm tearing my hair out here...

  • July 7, 2002 at 9:05 pm

    #432157

    What is the values for disk counters like PhysicalDisk()\Avg Disk Queue Length, PhysicalDisk()\Current Disk Queue Length. Did you run DBCC CHECKDB('DB_Name',REPAIR_REBUILD) WITH ALL_ERRORMSGS recently?

  • July 8, 2002 at 10:49 am

    #432194

    Is the database set to 65? sh_page errors are thing from SQL 6.5. Upon conversion it is easy to forget to change modes.

    sp_dbcmptlevel 'yourDBname'

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

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