Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    12

    Unanswered: 1105 & 3475 errors

    Greetings all again,

    I have a development database that went offline due to log segment errors while I was off last Friday. Below are the errors in the Sybase error log that occurred with the reboot of the Sybase server while I was away.

    00:00000:00001:2009/02/17 19:03:03.63 server Started UNDO pass for database 'CWT_DIR_TRAIN'. The total number of log records to process is 655192.
    00:00000:00001:2009/02/17 19:03:03.69 server Error: 1105, Severity: 17, State:3
    00:00000:00001:2009/02/17 19:03:03.69 server Can't allocate space for object 'syslogs' in database 'CWT_DIR_TRAIN' because 'logsegment' segment is full/has nofree extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.
    00:00000:00001:2009/02/17 19:03:03.69 server Error: 3475, Severity: 21, State:7
    00:00000:00001:2009/02/17 19:03:03.69 server There is no space available in SYSLOGS to log a record for which space has been reserved. This process will retry at intervals of one minute.

    I attempted all possible solutions mentioned in Sybooks online - dump transaction with truncate_only, dump transaction with no_log, alter database extend logsegment, however it all failed because I was getting messages that the CWT_DIR_TRAIN database was not recovered. Then I found this section on recovering from 1105 errors.

    Adaptive Server Enterprise 15.0 > Troubleshooting Guide: Error Messages Advanced Resolutions > Page Manager Errors (1100s) - attempted this set of SQL statements -

    Recovery for1105 errors on a user database
    Use the following procedure to correct 1105 errors on a user database which occur during recovery:

    Manually set the database’s status to no chkpt on recovery and single-user:

    WARNING! Because the database was marked suspect on the original recovery attempt, this procedure also resets some internal status bits to allow the database to recover normally. Therefore, do not use this procedure under any other circumstances to avoid additional serious problems.

    1> sp_configure "allow updates", 1
    2> go

    1> begin transaction
    2> go

    1> update master..sysdatabases
    2> set status = status & ~256
    3> where name = "<database_name>"
    4> go

    1> update master..sysdatabases
    2> set status = status | 4112
    3> where name = "<database_name>"
    4> go

    Check that each of the above update commands affected only one row. If more than one row was affected, issue a rollback transaction. Otherwise, commit the transaction and shut down Adaptive Server:

    1> commit transaction
    2> go


    1> shutdown
    2> go

    Restart Adaptive Server.

    Dump the transaction log with the no_log option and reset the database status:

    1> use master
    2> go


    1> dump tran <database_name> with no_log
    2> go

    1> sp_dboption <database_name>, "no chkpt", false
    2> go

    1> sp_dboption <database_name>, single, false
    2> go

    1> use <database_name>
    2> go

    1> checkpoint
    2> go

    1> sp_configure "allow updates", 0
    2> go

    However, when I rebooted the data server, to dump the transaction log, the server nows says the CWT_DIR_TRAIN database is now in single-user mode. I confirmed it with seeing this record from sp_who - 0 1 background NULL NULL 0 CWT_DIR_TRAIN MAINTENANCE TOKE 0.

    I think I just made the problem worse. I did not realize this solution was for Sybase ASE 15.0 and newer. I am working with Sybase ASE 12.5.3. Is there anyway I can recover the database entirely or do I have to drop it and recreate from scratch? Any further help will be appreciated.

    Patrick Quinn

    Carlson Wagonlit Travel Sybase DBA

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Did you manage to do a dump tran after changing the status?

    This command remove single-user mode
    sp_dboption <database_name>, single, false

  3. #3
    Join Date
    Nov 2008
    Posts
    12
    I attempted to dump the transaction log, but it failed when I tried to put the database out of single-user mode. I am running out of options.

    Patrick Quinn

    Carlson Wagonlit Travel Sybase DBA

    sp_dboption CWT_DIR_TRAIN, "single", false
    GO

    Server Message: Number 921, Severity 14
    Server 'SYB_TEST', Procedure 'sp_dboption', Line 684:
    Database 'CWT_DIR_TRAIN' has not been recovered yet - please wait and try again.
    Server Message: Number 3512, Severity 16
    Server 'SYB_TEST', Procedure 'sp_dboption', Line 684:
    Cannot checkpoint database 'CWT_DIR_TRAIN' because its status is: not usable.
    Database option 'single user' turned OFF for database 'CWT_DIR_TRAIN'.
    Running CHECKPOINT on database 'CWT_DIR_TRAIN' for option 'single user' to take effect.
    CHECKPOINT command failed. Run the CHECKPOINT command in database 'CWT_DIR_TRAIN' for the change to take effect.
    (1 row affected)
    (return status = 0)

  4. #4
    Join Date
    Feb 2007
    Location
    India
    Posts
    56

    Re: 1105 & 3475 errors

    Hi,
    Can we have 1) sp_helpdb output. 2) select name,status from master..sysdatabases output.

    So in this case we have to rebuild the log by putting the db in logsuspend mode.
    Regards,
    Naveen.
    Sybase DBA-Consultant

  5. #5
    Join Date
    Nov 2008
    Posts
    12
    Here is the output of your inquiry.

    sp_helpdb
    go

    APM 300.0 MB sa 16 Feb 08, 2006 select into/bulkcopy/pllsort, trunc log on chkpt
    APPLOG 300.0 MB sa 13 Jun 29, 2005 select into/bulkcopy/pllsort
    CWMM 230.0 MB sa 11 Jun 29, 2005 trunc log on chkpt
    CWMM_51 1524.0 MB sa 15 Feb 08, 2006 select into/bulkcopy/pllsort, trunc log on chkpt, no free space acctg
    CWT_DIR 11236.0 MB sa 9 Jun 24, 2004 select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full
    CWT_DIR_BENCH 9713.0 MB sa 18 Mar 07, 2007 select into/bulkcopy/pllsort
    CWT_DIR_TEST 21471.0 MB sa 4 Nov 26, 2007 abort tran on log full
    CWT_DIR_TRAIN 34206.0 MB sa 6 Nov 27, 2007 not recovered, offline
    CWT_HISTORICAL 350.0 MB sa 8 May 03, 2004 not recovered, offline
    DMS_CWT 300.0 MB sa 5 Apr 28, 2004 select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full
    ECARD 120.0 MB sa 12 Jun 29, 2005 not recovered, offline
    TIMETRACKING 25.0 MB sa 10 Jan 06, 2005 no options set
    audit_data 2500.0 MB sa 7 Sep 17, 2008 select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full, mixed log and data
    dbccdb 200.0 MB sa 31516 Mar 15, 2006 no options set
    gemalarms 250.0 MB sa 17 Mar 24, 2006 select into/bulkcopy/pllsort, trunc log on chkpt
    master 60.0 MB sa 1 Apr 28, 2004 mixed log and data
    model 2.0 MB sa 3 Apr 28, 2004 mixed log and data
    sybmgmtdb 52.5 MB sa 14 Oct 01, 2005 select into/bulkcopy/pllsort, trunc log on chkpt
    sybsystemdb 25.0 MB sa 31513 Apr 28, 2004 mixed log and data
    sybsystemprocs 160.0 MB sa 31514 Apr 28, 2004 trunc log on chkpt, mixed log and data
    tempdb 2050.0 MB sa 2 Feb 19, 2009 select into/bulkcopy/pllsort, trunc log on chkpt, abort tran on log full, mixed log and data

    select name,status from master..sysdatabases
    go

    APM 12
    APPLOG 4
    CWMM 8
    CWMM_51 12
    CWT_DIR 12
    CWT_DIR_BENCH 4
    CWT_DIR_TEST 0
    CWT_DIR_TRAIN 323
    CWT_HISTORICAL 323
    DMS_CWT 12
    ECARD 323
    TIMETRACKING 0
    audit_data 12
    dbccdb 0
    gemalarms 12
    master 0
    model 0
    sybmgmtdb 12
    sybsystemdb 0
    sybsystemprocs 8
    tempdb 12

  6. #6
    Join Date
    Dec 2013
    Posts
    3

    Found a solution for the 1105 & 3475 errors in my case

    I know this is an old thread - but since it didn't seem to be fully answered/resolved, and the problem being identical to what I experienced, I figure I'd post the solution I found to save someone else's time.

    First, my env:

    Adaptive Server Enterprise/15.0.3/EBF 16550 ESD#1/P/NT (IX86)/Windows 2003

    Problem:

    Error: 1105, Severity: 17, State: 7
    Can't allocate space for object 'syslogs' in database 'XXX' because 'logsegment' segment is full/has no free extents etc.
    followed by:
    Error: 3475, Severity: 21, State: 7
    There is no space available in SYSLOGS to log a record for which space has been reserved in database ...

    Behavior:

    First, processes become blocked with PLC sleep status / latch locks, including replication; long transactions and other jobs seem to be active, including replication, but don't seem to make progress; log becomes full (dump on threshold didn't work any longer), but there is no warning, and sp_helpdb reports it as if log has plenty of space. On ASE restart, db XXX can't be recovered, becomes unusable.

    What I tried, TO NO AVAIL:

    Sybase solution recommended for CR #290746, at:
    Sybase Inc - Search

    Forum recommendations found at:

    Yahoo Groups
    Sybase FAQ: 7/19 - ASE Admin (4 of 7)
    https://groups.google.com/forum/#!to...on/CLjI32LH2lw
    http://www.dbforums.com/sybase/99895...slogshold.html
    http://www.dbforums.com/sybase/98258...p-problem.html
    http://www.dbforums.com/sybase/99312...er-failed.html

    What I did, and WORKED:

    - based on Manish Negandhi's recommendation at:
    Log segment full - thanks Manish!
    and to a certain extent validated by Sybase.com.

    use master
    go

    sp_configure "allow updates", 1
    go

    select status from sysdatabases where dbid=5 (save this for db_option at the end only; it includes 64 - not recovered)
    go

    begin tran
    update sysdatabases set status =-32768 where dbid = 5 -- to avoid recovery on startup for a single db
    go
    -- if the above affects only 1 row, proceed
    commit tran
    go

    checkpoint
    go
    shutdown with nowait
    go

    On restart, database is not recovered, but accessible.

    disk init name = 'logXXX'... etc
    go
    alter database XXX log on logXXX =...
    go
    update sysdatabases set status = 0 where dbid = 5 -- back to normal startup
    go

    followed again by checkpoint master and shutdown with nowait

    Now on startup, db goes through recovery, and finishes correctly (takes some time).
    Log now shows lots of data. I can dump tran, etc.
    Finally, I can use sp_dboption to reset my db options back what they were.

    Also, don't forget to:

    sp_configure "allow updates", 0
    go

  7. #7
    Join Date
    May 2014
    Posts
    11

    Solved?

    Greetings...has this issue been solved (1105 & 3475 errors)? I am in an almost exact situation and cannot find a way to get my database back online. I tried getting out of single user mode, running checkpoints, etc. I get an odd message after this:

    Code:
    database option 'single user' turned OFF for database 'XXX'
    running CHECKPOINT on database 'XXX' for open singal user to take effect.
    Msg 934, Level 14, State 1:
    Server 'AAA', procedure 'sp_dboption, line 684
    Database 'XXX' is already open and can only have one user at a time.
    Msg 3512, level 16, state 1:
    Server 'AAA' Procedure 'sp_dboption', line 684:
    cannot checkpoint database 'XXX' because its status is 'unusable'
    CHECKPOINT commadn failed.  Run the CHECKPOINT command in database ''XXX" for the change to take affect.
    I cannot run checkpoint on the database because I cannot get it back online. The above message seems to indicate its turning off single user mode for my db???? When I run an sp_who, there is an SPID that has accesss to the DB and the DB is in 'MAINTENANCE TOKE' mode.

    Any ideas? Help?

    thanks,
    Bill

  8. #8
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    Okay so now you problem is the you have some connection using the the db, and you cant change the single user.

    This is kind of last resort measure. ( backup everythig you can before )

    Set update to system tables on.

    update sysdatabases table on the DB you want. Change status field. ( right now you must have a value over 4096 change it to 12 ( which is "bulk copy" and "trunc log on chekpoint " on ).

    Set update to system tables off.

    Bounce.

    Now the DB should not be in single user mode. Get that log cleared.

    Let me know if it worked.

  9. #9
    Join Date
    May 2014
    Posts
    11
    Thank you very much for your response. I appreciate it.

    Setting the status to "12" is done how? set status = 12? I have seen (as in the original post) where the status is set various ways such as:
    set status = status & ~256
    set status = status | 4112.

    Also, when you say "bounce" I am assuming you are telling me to stop/start sybase?

    What are your thoughts on Andreiu's solution (one post before mine, which I did not see before joining dbForums yesterday)?

    Thank you again,
    Bill

  10. #10
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi
    Andrieu solution is valid.
    It extends the log. His way will work if you have disk space and dont mind extending the log.

    What i said had a different purpose, was to take out the "single user" so that you can dump the log avoiding to extend.


    As for the status i believe is set status = 12 ( the field is smallint, plus if you see Andrew he als sets status =0 ) But check this in detail.

    Bounce = restart instance

Posting Permissions

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