Post reply

SQL Log Error: 1501, Severity: 20, State: 1 Sort Failure

  • August 24, 2005 at 1:49 pm

    #110904

    Hi Everyone,

    I am getting "Error: 1501, Severity: 20, State: 1 Sort Failure" on production machine and I am completely stumped on this one. Could not find any MS article in KB. However, did find a potential soultion to increase 'Minimum query memory' which I bumped from 1024 KB to 2048 KB but it did not help either.

    All maintenance jobs are running during off-peak hours on daily basis means database backup dumps, reindexing, checkdb etc. It is a SQL Server Enterprise 2000 with SP3a on Windows 2003 Enterpise Edition with 8 processors and 4GB RAM. I would appreciate if any help can be provided in this regard since severity level is high.

    Thanks

    Faisal 

  • August 24, 2005 at 9:55 pm

    #584852

    Hi Faisal,

    Why u are using reindexing on nightly basis.Dont u know it use too much of process.

    This type of error occurs When creating a high level index structure, Adaptive Server is unable to build a parent node in the ancestor list.

    or

    The server is unable to allocate and initialize space for managing the sort's buffers

    or

    The server is building indexes and the length of a row in an overflow page is either zero or greater than the size of a page.

    When this error occurs, no index is created.

    The configuration parameter number of sort buffers controls how Adaptive Server uses memory during sorting. number of sort buffers controls the sort buffer size; its default value is 500. Changing the value of number of sort buffers can cause Error 1501.

    1. Determine the current value of number of sort buffers:

      1> sp_configure "number of sort buffers"2> go

    2. Change the value, perhaps back to the default value of 500 (recommended except when creating indexes in parallel):

      1> sp_configure "number of sort buffers", new_value2> go

      or

    3. use sp_configure "max server memory (MB)".      

           go

    sp_configure "max server memory (MB)",new_value

    go

     

    hope this helps u

    from

    killer

     

     

     

  • August 24, 2005 at 10:06 pm

    #584855

    Hi faisal,

    For checking the real error excute

    dbcc checktable(tablename)

    dbcc checkalloc

     

    hope this help u

     

    from

    killer

  • August 24, 2005 at 10:20 pm

    #584856

    Hi, Thanks for your input. I am sorry the reindexing job is running on every Sunday, it was mistake on my part to tell on daily basis. I tried to use the following command in QA but getting the error mentioned below.

    sp_configure "number of sort buffers"

    Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79

    The configuration option 'number of sort buffers' does not exist, or it may be an advanced option.

     

    Valid configuration options are:

    Also you suggested to change max server memory but the server is configured with the default dynamic use of memory.

    Thanks

    Faisal

  • August 25, 2005 at 12:01 am

    #584864

    Hi,

    Sorry that work on sybase.

    just execute this

    USE master

    EXEC sp_configure 'show advanced option', '1'

    EXEC sp_configure

    what memory u set for indexes

     index create memory (KB) min  and max

     

    from

    killer

  • August 25, 2005 at 9:20 am

    #585041

    I did not change anything to that parameter but here it is what I got from EXEC sp_configure, that shoudl be the default settings as 0 says:

    name                              minimum     maximum      config_value  run_value  

    index create memory (KB)   704           2147483647  0                 0

  • August 25, 2005 at 2:12 pm

    #585171

    I felt the 'Sybase DBA' in rising back to life ...

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • August 25, 2005 at 2:16 pm

    #585172

    This is SQL Server problem and I apologize if gentleman is giving recommendations on Sybase. I would like to know any solution you guys can suggest. Apprecite it.

    Thanks

    Faisal

  • August 25, 2005 at 2:52 pm

    #585185

    The general rule "suggested by microsoft" for large databases is to keep Min Server Memory and max Server memory to same value which should be as high as possible on server. So, if your server has 4GB of RAm keep these parameters at 3GB.

    You need to use sp_configure to change these.

    Anurag Nayar

  • August 25, 2005 at 9:07 pm

    #585268

    Hi Faisal,

    I already apologised for my mistake.

    But yes this error occurs when u reindexing on less memory.So, U can increase ur max server memory.

    Hope this helps u.

    From

    Killer

Viewing 10 posts - 1 through 9 (of 9 total)

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