Results 1 to 2 of 2

Thread: SQL Server 7 hanging Windows NT

  1. #1
    Lee Revilles Guest

    SQL Server 7 hanging Windows NT


    I was wondering if anyone could provide me with some help, to do with a problem we are experiencing with SQL Server 7 and Windows NT 4.

    We have a database that we have imported data into and are trying to build some indexes on one of the tables. THe database size is about 4GB and the table contains 21,000,000 records. When we try to build the indexes SQL server starts off OK, but after about five minutes all drive activity stops, the server stops responsing, and we need to reboot.

    The machine has 2 P3 800 processers and 768Mb of RAM. NT is running on SP6 and SQL Server is running on SP3. We were initially running with 512 MB of RAM, but increased it because we thought the memory was a problem. The Server is not a dedicated SQL Server.

    I have used performance monitor when running the query in SQL Analyser and have noted the following counters:

    Total Server Memory

    This starts off about 208000KB and creeps up to about 625000KB just before the server hangs.

    Free Memory Available

    This starts off high and is about 6MB just before the server hangs.

    Page Reads Sec

    Normally 370 and 410

    Page Writes Sec

    157

    Buffer Cache hit Ratio

    99.1


    I have tried setting the SQL Server max memory option to about 600 MB, and also let SQL Server dynamically allocate it.



  2. #2
    sethu sr Guest

    SQL Server 7 hanging Windows NT (reply)

    Hi Lee

    i also faced problems with huge tables like 4 gb ,but not nt hang
    need more info on this nt hang means (have to checked on the task
    manager if the sql server process is consuming cpu ,if it consumes
    cpu then its not a hang ,any error reported in the sql server logs.

    these info will be usefull to you.

    usually when you a load data to a large table and try to create
    a index the index will fail ,the following will the cause the table
    will corrupted (how to find a table is corrupted)

    run dbcc checktable 'tablename' if corrupted you will get the following
    messages.

    Table Corrupt: Object ID 1125579048, index ID 0, page (1:11370), row 17. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 4229 and 199.
    Server: Msg 8928, Level 16, State 1, Line 1
    Object ID 1125579048, index ID 0: Page (1:67856) could not be processed. See other errors for details.
    Server: Msg 8944, Level 16, State 1, Line 1
    Table Corrupt: Object ID 1125579048, index ID 0, page (1:67856), row 3. Test (columnOffsets->offTbl [varColumnNumber] <= (nextRec - pRec)) failed. Values are 4278 and 193.
    DBCC results for &#39;tblPatMedHist&#39;.
    There are 3852181 rows in 96730 pages for object &#39;tblPatMedHist&#39;.
    CHECKTABLE found 0 allocation errors and 4 consistency errors in table &#39;tblPatMedHist&#39; (object ID 1125579048).
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (swi_profile.dbo.tblPatMedHist ).

    so you need to correct this before doing the index ( i hope this info
    may be useful to you, i also suffered with uploads ...in big tables.

    Sethu


    ------------
    Lee Revilles at 7/10/01 5:38:32 AM


    I was wondering if anyone could provide me with some help, to do with a problem we are experiencing with SQL Server 7 and Windows NT 4.

    We have a database that we have imported data into and are trying to build some indexes on one of the tables. THe database size is about 4GB and the table contains 21,000,000 records. When we try to build the indexes SQL server starts off OK, but after about five minutes all drive activity stops, the server stops responsing, and we need to reboot.

    The machine has 2 P3 800 processers and 768Mb of RAM. NT is running on SP6 and SQL Server is running on SP3. We were initially running with 512 MB of RAM, but increased it because we thought the memory was a problem. The Server is not a dedicated SQL Server.

    I have used performance monitor when running the query in SQL Analyser and have noted the following counters:

    Total Server Memory

    This starts off about 208000KB and creeps up to about 625000KB just before the server hangs.

    Free Memory Available

    This starts off high and is about 6MB just before the server hangs.

    Page Reads Sec

    Normally 370 and 410

    Page Writes Sec

    157

    Buffer Cache hit Ratio

    99.1


    I have tried setting the SQL Server max memory option to about 600 MB, and also let SQL Server dynamically allocate it.



Posting Permissions

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