Results 1 to 6 of 6
  1. #1
    Join Date
    May 2014
    Posts
    11

    Unanswered: Dump bad database

    Greetings all,

    I have tried everything to my (limited) knowledge. I am basically stuck.

    I have a development database (using sybase 12.5). I have a similar issue as to one posted recently by "giyoko" (http://www.dbforums.com/sybase/16383...75-errors.html). What I really want to do is to just dump this database and start clean. The problem is, every time I start up sybase a MAINTENANCE TOKEN process exists and has ahold of my database...so when I try to drop and start new I get errors (database in use). How can I get rid of the MAINTENANCE TOKEN process (kill doesnt work) and start clean?

    For background, I followed suggestions as were detailed in the '1105 & 3475 errors'. I also posted in that thread and received some good suggestions from a user by the name of Cattarunas. The thing that keeps getting in the way is that MAINTENANCE TOKEN process. I really just want to start over clean. I am hoping there is a way to do this.

    Any help is appreciated.
    thanks,
    Bill

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

    So if recall, your situation had some special details. You were locked out of your db, bc it was in single user. How is that?

    Do you want to dump? Or just drop the db, recreate and load from bkp?

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Not sure what your exact problem is or what you try to achieve but try these

    To truncate the log without making a backup copy:
    dump tran database_name
    with truncate_only

    To truncate a log that is filled to capacity.
    dump tran database_name
    with no_log

    To get rid of the database
    set the status to 320 (mark suspect) e.g.
    update sysdatabases set status=320 where name='database_name'
    You might have to restart Sybase after setting the status depending on your problem.
    Then drop the database with dbcc dbrepair (database_name, dropdb)

    If you don't do regular tran log backups I suggest setting DB option "trunc log on chkpt"
    Another good option to prevent one transaction from halting your DB is "abort tran on log full"
    Last edited by pdreyer; 05-23-14 at 08:22.

  4. #4
    Join Date
    May 2014
    Posts
    11
    Hello Catarrunas and pdreyer,

    First and foremost, thank you both for responding to my post. I appreciate your help.

    This morning, I went back to try another method (again) to see if I can get the datbase up such that I can extend the log segment. I followed this procedure:

    Code:
    use master
    go
    
    sp_configure "allow updates", 1
    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
    When I rebooted the system (restarting sybase), the database seemed to come up in a more stable state (so it seemed). Instead of seeing the usual messages in the log file:

    Code:
    00:00000:00001:2009/02/17 19:03:03.63 server Started UNDO pass for database XXX. 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 XXX 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 was that sybase did not try to recover my XXX database. Ok, so I thought "this is good. I will try to extend the database and see if the database will come back up". I ran the following procedure (and received the following error message):

    Code:
    > disk init ame='XXXLOG1', physname='/somedir/XXXLOG1.dat', size='2G'
    > go
    > alter database XXX log on XXXLOG1='1.5G' with override
    >go
    ...Extending database by 786432 pages (1536.0 mb) to disk XXXLOG1
    Msg 3908 level 16, State 1: Server 'MYSERVER', Line 1:
    Attempt to BEGIN TRANSACTION in database 'XXX' failed because database is in BYPASS RECOVERY mode
    So, it looks like the extend failed. How can I get around THAT error? If I can extend the database it might solve my issues. Otherwise, I will need to go back to the plan of just dumping that XXX database and restarting (of course, that MAINTENANCE TOKEN issue still needs to be discussed).

    I appreciate your help.
    Bill

  5. #5
    Join Date
    May 2014
    Posts
    11

    New error

    Hello Catarrunas and pdreyer,

    First and foremost, thank you both for responding to my post. I appreciate your help.

    This morning, I went back to try another method (again) to see if I can get the datbase up such that I can extend the log segment. I followed this procedure:

    Code:
    use master
    go
    
    sp_configure "allow updates", 1
    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
    When I rebooted the system (restarting sybase), the database seemed to come up in a more stable state (so it seemed). Instead of seeing the usual messages in the log file:

    Code:
    00:00000:00001:2009/02/17 19:03:03.63 server Started UNDO pass for database XXX. 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 XXX 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 was that sybase did not try to recover my XXX database. Ok, so I thought "this is good. I will try to extend the database and see if the database will come back up". I ran the following procedure (and received the following error message):

    Code:
    > disk init ame='XXXLOG1', physname='/somedir/XXXLOG1.dat', size='2G'
    > go
    > alter database XXX log on XXXLOG1='1.5G' with override
    >go
    ...Extending database by 786432 pages (1536.0 mb) to disk XXXLOG1
    Msg 3908 level 16, State 1: Server 'MYSERVER', Line 1:
    Attempt to BEGIN TRANSACTION in database 'XXX' failed because database is in BYPASS RECOVERY mode
    So, it looks like the extend failed. How can I get around THAT error? If I can extend the database it might solve my issues. Otherwise, I will need to go back to the plan of just dumping that XXX database and restarting (of course, that MAINTENANCE TOKEN issue still needs to be discussed).

    I appreciate your help.
    Bill

  6. #6
    Join Date
    May 2014
    Posts
    11

    new one

    Hello Catarrunas and pdreyer,

    First and foremost, thank you both for responding to my post. I appreciate your help.

    This morning, I went back to try another method (again) to see if I can get the datbase up such that I can extend the log segment. I followed this procedure:

    Code:
    use master
    go
    
    sp_configure "allow updates", 1
    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
    When I rebooted the system (restarting sybase), the database seemed to come up in a more stable state (so it seemed). Instead of seeing the usual messages in the log file:

    Code:
    00:00000:00001:2009/02/17 19:03:03.63 server Started UNDO pass for database XXX. 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 XXX 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 was that sybase did not try to recover my XXX database. Ok, so I thought "this is good. I will try to extend the database and see if the database will come back up". I ran the following procedure (and received the following error message):

    Code:
    > disk init ame='XXXLOG1', physname='/somedir/XXXLOG1.dat', size='2G'
    > go
    > alter database XXX log on XXXLOG1='1.5G' with override
    >go
    ...Extending database by 786432 pages (1536.0 mb) to disk XXXLOG1
    Msg 3908 level 16, State 1: Server 'MYSERVER', Line 1:
    Attempt to BEGIN TRANSACTION in database 'XXX' failed because database is in BYPASS RECOVERY mode
    So, it looks like the extend failed. How can I get around THAT error? If I can extend the database it might solve my issues. Otherwise, I will need to go back to the plan of just dumping that XXX database and restarting (of course, that MAINTENANCE TOKEN issue still needs to be discussed).

    I appreciate your help.
    Bill

Posting Permissions

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