Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004

    Exclamation Unanswered: Help! Having problem with spt_values table in master

    Sorry for the really long post, but I need help. I am new to this forum and hopefully someone here can help me, I am working as DBA, but not a master of Sybase. Here is some background:

    Machine: Sun E5500
    CPUs: 8
    Mem: 8gb
    OS: Solaris 8
    ASE: 12.5 (32bit)
    Databases: 7 (~100gb)

    I was working this week on doing some DBCC, as part of a plan to migrate to 64bit sybase. I started with one of the larger ones, that is not being used on a regular basis. I have been doing irregular manual backups on this for a while and it may be my downfall. The DBCC (database check) ran fine -- it took about 1.5days to complete. However, what I didn't notice was a continuing error message in the database log file:

    05:00000:00016:2004/02/13 18:06:37.87 server Increase the config parameter 'number of open indexes' to avoid descriptor reuse. Reuse may result in performance degradation.

    A day after the DBCCs (today) I got paged that no one could do simple select statements. And also counldn't get any lock information -- we use Embarcadero's DBartisan as one of our management tools. The error we received that tool is:

    Data is not available for process Error: Cannot find an available index descriptor for an index. Increase the the value of 'number of open indexes'. If sp_configure fails due lack of index descriptors, edit the configuration file and restart the server.

    DBartisan also states that <u>master.dbo.spt_values</u> is not found. But I don't think this is a valid error, because it does exist.

    These are the steps I am taking right now to recover from this:

    1) Backing up as much of the data as I can. BCP does not seem to have any problems.
    2) I have been using sybperl library and to create the BCP scripts. I get similar errors for 'open indexes' when trying to extra the schema. Luckily it seems to get all the table names for bcping.
    3) Trying to change the value for 'number of open indexes' on the fly and see if it works; otherwise shutting down the server(just sybase at first) and restarting with the changes in the 'number of open indexes'.

    My questions are:
    1) Why did this happen? I've looked around and haven't found any resonable answer.
    2) Will the stupid server come back up without any major problems?
    3) Is master corrupted? or should I do anything with spt_values table?


    Also here is my current config file:

    [Configuration Options]

    [General Information]

    recovery interval in minutes = DEFAULT
    print recovery information = DEFAULT
    tape retention in days = DEFAULT

    [Cache Manager]
    number of oam trips = DEFAULT
    number of index trips = DEFAULT
    memory alignment boundary = DEFAULT
    global async prefetch limit = DEFAULT
    global cache partition number = DEFAULT

    [Named Cache:default data cache]
    cache size = DEFAULT
    cache status = default data cache
    cache replacement policy = DEFAULT
    local cache partition number = DEFAULT

    [Meta-Data Caches]
    number of open databases = DEFAULT
    number of open objects = 2000
    open object spinlock ratio = DEFAULT
    number of open indexes = 2000
    open index hash spinlock ratio = DEFAULT
    open index spinlock ratio = DEFAULT
    partition groups = DEFAULT
    partition spinlock ratio = DEFAULT

    [Disk I/O]
    disk i/o structures = DEFAULT
    number of large i/o buffers = DEFAULT
    page utilization percent = DEFAULT
    number of devices = 200
    disable disk mirroring = DEFAULT
    allow sql server async i/o = DEFAULT

    disable character set conversions = DEFAULT

    enable unicode normalization = DEFAULT
    enable surrogate processing = DEFAULT
    enable unicode conversions = DEFAULT
    size of unilib cache = DEFAULT

    [Network Communication]
    default network packet size = 4096
    max network packet size = 4096
    remote server pre-read packets = DEFAULT
    number of remote connections = DEFAULT
    number of remote logins = DEFAULT
    number of remote sites = DEFAULT
    max number network listeners = DEFAULT
    tcp no delay = DEFAULT
    allow sendmsg = DEFAULT
    syb_sendmsg port number = DEFAULT
    allow remote access = DEFAULT

    [O/S Resources]
    max async i/os per engine = DEFAULT
    max async i/os per server = DEFAULT

    [Parallel Query]
    number of worker processes = DEFAULT
    memory per worker process = DEFAULT
    max parallel degree = DEFAULT
    max scan parallel degree = DEFAULT

    [Physical Resources]

    [Physical Memory]
    max memory = 2000000
    additional network memory = 79872
    shared memory starting address = DEFAULT
    allocate max shared memory = DEFAULT
    dynamic allocation on demand = DEFAULT
    lock shared memory = DEFAULT
    heap memory per user = DEFAULT

    max online engines = 6
    number of engines at startup = 6

    [SQL Server Administration]
    procedure cache size = 1000000
    default database size = DEFAULT
    identity burning set factor = DEFAULT
    allow nested triggers = DEFAULT
    allow updates to system tables = DEFAULT
    default fill factor percent = DEFAULT
    default exp_row_size percent = DEFAULT
    number of mailboxes = DEFAULT
    number of messages = DEFAULT
    number of alarms = DEFAULT
    number of pre-allocated extents = 4
    event buffers per engine = DEFAULT
    cpu accounting flush interval = DEFAULT
    i/o accounting flush interval = DEFAULT
    sql server clock tick length = DEFAULT
    runnable process search count = DEFAULT
    i/o polling process count = DEFAULT
    time slice = DEFAULT
    cpu grace time = DEFAULT
    number of sort buffers = DEFAULT
    size of auto identity column = DEFAULT
    identity grab size = DEFAULT
    housekeeper free write percent = DEFAULT
    enable housekeeper GC = DEFAULT
    allow resource limits = DEFAULT
    number of aux scan descriptors = DEFAULT
    SQL Perfmon Integration = DEFAULT
    allow backward scans = DEFAULT
    license information = DEFAULT
    enable sort-merge join and JTC = DEFAULT
    abstract plan load = DEFAULT
    abstract plan dump = DEFAULT
    abstract plan replace = DEFAULT
    abstract plan cache = DEFAULT
    text prefetch size = DEFAULT
    enable HA = DEFAULT

    [User Environment]
    number of user connections = DEFAULT
    stack size = DEFAULT
    stack guard size = DEFAULT
    permission cache entries = DEFAULT
    user log cache size = DEFAULT
    user log cache spinlock ratio = DEFAULT

    [Lock Manager]
    number of locks = 30000
    deadlock checking period = DEFAULT
    lock spinlock ratio = DEFAULT
    lock address spinlock ratio = DEFAULT
    lock table spinlock ratio = DEFAULT
    lock hashtable size = DEFAULT
    lock scheme = DEFAULT
    lock wait period = DEFAULT
    read committed with lock = DEFAULT
    print deadlock information = DEFAULT
    deadlock retries = DEFAULT
    page lock promotion HWM = DEFAULT
    page lock promotion LWM = DEFAULT
    page lock promotion PCT = DEFAULT
    row lock promotion HWM = DEFAULT
    row lock promotion LWM = DEFAULT
    row lock promotion PCT = DEFAULT

    [Security Related]
    systemwide password expiration = DEFAULT
    audit queue size = DEFAULT
    curread change w/ open cursors = DEFAULT
    allow procedure grouping = DEFAULT
    select on syscomments.text = DEFAULT
    auditing = DEFAULT
    current audit table = DEFAULT
    suspend audit when device full = DEFAULT
    enable row level access = DEFAULT
    check password for digit = DEFAULT
    minimum password length = DEFAULT
    maximum failed logins = DEFAULT
    enable ssl = DEFAULT
    unified login required = DEFAULT
    use security services = DEFAULT
    msg confidentiality reqd = DEFAULT
    msg integrity reqd = DEFAULT
    secure default login = DEFAULT

    [Extended Stored Procedure]
    esp unload dll = DEFAULT
    esp execution priority = DEFAULT
    esp execution stacksize = DEFAULT
    xp_cmdshell context = DEFAULT
    start mail session = DEFAULT

    [Error Log]
    event logging = DEFAULT
    log audit logon success = DEFAULT
    log audit logon failure = DEFAULT
    event log computer name = DEFAULT

    [Rep Agent Thread Administration]
    enable rep agent threads = DEFAULT

    [Component Integration Services]
    enable cis = DEFAULT
    cis connect timeout = DEFAULT
    cis bulk insert batch size = DEFAULT
    max cis remote connections = DEFAULT
    cis packet size = DEFAULT
    cis cursor rows = DEFAULT
    enable file access = DEFAULT
    cis bulk insert array size = DEFAULT
    enable full-text search = DEFAULT
    cis rpc handling = DEFAULT

    [Java Services]
    enable java = DEFAULT
    size of process object heap = DEFAULT
    size of shared class heap = DEFAULT
    size of global fixed heap = DEFAULT
    number of java sockets = DEFAULT
    enable enterprise java beans = DEFAULT

    [DTM Administration]
    enable DTM = DEFAULT
    enable xact coordination = DEFAULT
    xact coordination interval = DEFAULT
    number of dtx participants = DEFAULT
    strict dtm enforcement = DEFAULT
    txn to pss ratio = DEFAULT
    dtm lock timeout period = DEFAULT
    dtm detach timeout period = DEFAULT

    dump on conditions = DEFAULT
    maximum dump conditions = DEFAULT
    number of ccbs = DEFAULT
    caps per ccb = DEFAULT
    average cap size = DEFAULT

    enable monitoring = DEFAULT
    sql text pipe active = DEFAULT
    sql text pipe max messages = DEFAULT
    plan text pipe active = DEFAULT
    plan text pipe max messages = DEFAULT
    statement pipe active = DEFAULT
    statement pipe max messages = DEFAULT
    errorlog pipe active = DEFAULT
    errorlog pipe max messages = DEFAULT
    deadlock pipe active = DEFAULT
    deadlock pipe max messages = DEFAULT
    wait event timing = DEFAULT
    process wait events = DEFAULT
    object lockwait timing = DEFAULT
    SQL batch capture = DEFAULT
    statement statistics active = DEFAULT
    per object statistics active = DEFAULT
    max SQL text monitored = DEFAULT

  2. #2
    Join Date
    Nov 2002
    Are you able to access master.dbo.spt_values using isql ? (the problem is perhaps coming from DBArtisan) ?

    Did you execute dbcc checktable and dbcc alloctable in spt_values ?

    Did you increase your parameter "open indexes" ? Regarding the time spent to execute a dbcc, your database is quite big. Please, check this value using sp_sysmon or sp_count_metadata. What's the exact version of your ASE ? before ASE 12.5, the parameters "number of open..." are static and need a reboot

Posting Permissions

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