Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    22

    Arrow Unanswered: Resize Transaction Log

    I want to make my transaction log smaller so I can dump to tape, Sybase is 11.9.2 and partition is raw disk.

    Any ideas??

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya,

    This old posting from Bret might help;
    http://www.isug.com/Sybase_FAQ/ASE/s....2.html#1.2.12

    Cheers
    Willy

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    22
    Cheers Willy,

    I was aware of that link however it refers to shrinking a database rather than a transaction log, I thought there may be an easier way of reducing the transaction log, this is what I did:

    I knocked sybase down to single user mode and truncated the transaction log so that then the log should be empty. Then I removed the last entry out of sysusages for the tranlog and rebooted sybase. This produced the following error upon load...

    #######################################

    00:00000:00001:2003/06/09 22:06:35.31 server Recovering database 'notproduction_db'.
    00:00000:00001:2003/06/09 22:06:35.31 server Error: 806, Severity: 21, State: 1
    00:00000:00001:2003/06/09 22:06:35.31 server Could not find virtual page for lo
    gical page 40245242 in database 'notproduction_db'.
    00:00000:00001:2003/06/09 22:06:35.32 server Error: 3414, Severity: 21, State:
    1
    00:00000:00001:2003/06/09 22:06:35.32 server Database 'notproduction_db' (dbid 5): Reco
    very failed. Check the SQL Server errorlog for further information as to the cau
    se.
    00:00000:00001:2003/06/09 22:06:35.32 server Recovery complete.
    00:00000:00001:2003/06/09 22:06:35.32 server SQL Server's default sort order is
    :

    ####################################

    However reinserting the last entry back into the sysusages table allowed my database to load up okay (after reseting the status in sysdatabases).

    I also tried setting segmap to 0 as suggested but this had no real affect on anything (that I could tell).

    Then I set the dbcc traceon command as suggested in the link but the output produced was just way too much for me to wade through (I stopped the output when it reached around 30Mb text file!!).

    So I suppose where I am now is between a rock and a hard place, I have managed to screw my database so that it just wont load (fortunatley only development ). So basically I am unaware of anyone who has successfuly shrunk the size of there transaction log in Sybase 11.9.* - I was hoping for an easy solution but its fsat looking like there is not one..

    Any assistance would be much much appreciated.......
    Last edited by syb_fkup; 06-12-03 at 16:02.

  4. #4
    Join Date
    Jan 2003
    Posts
    62
    I don't think there is a way to shrink the log. Even if it exist, it's internally controlled by ASE (or rather how it's being engineered).
    I've seen when there's no open transaction, the dump tran with no_log does not remove all the inactive portion of the log, even after an explicit checkpoint.

    -Tezza.

    Originally posted by syb_fkup
    Cheers Willy,

    I was aware of that link however it refers to shrinking a database rather than a transaction log, I thought there may be an easier way of reducing the transaction log, this is what I did:

    I knocked sybase down to single user mode and truncated the transaction log so that then the log should be empty. Then I removed the last entry out of sysusages for the tranlog and rebooted sybase. This produced the following error upon load...

    #######################################

    00:00000:00001:2003/06/09 22:06:35.31 server Recovering database 'notproduction_db'.
    00:00000:00001:2003/06/09 22:06:35.31 server Error: 806, Severity: 21, State: 1
    00:00000:00001:2003/06/09 22:06:35.31 server Could not find virtual page for lo
    gical page 40245242 in database 'notproduction_db'.
    00:00000:00001:2003/06/09 22:06:35.32 server Error: 3414, Severity: 21, State:
    1
    00:00000:00001:2003/06/09 22:06:35.32 server Database 'notproduction_db' (dbid 5): Reco
    very failed. Check the SQL Server errorlog for further information as to the cau
    se.
    00:00000:00001:2003/06/09 22:06:35.32 server Recovery complete.
    00:00000:00001:2003/06/09 22:06:35.32 server SQL Server's default sort order is
    :

    ####################################

    However reinserting the last entry back into the sysusages table allowed my database to load up okay (after reseting the status in sysdatabases).

    I also tried setting segmap to 0 as suggested but this had no real affect on anything (that I could tell).

    Then I set the dbcc traceon command as suggested in the link but the output produced was just way too much for me to wade through (I stopped the output when it reached around 30Mb text file!!).

    So I suppose where I am now is between a rock and a hard place, I have managed to screw my database so that it just wont load (fortunatley only development ). So basically I am unaware of anyone who has successfuly shrunk the size of there transaction log in Sybase 11.9.* - I was hoping for an easy solution but its fsat looking like there is not one..

    Any assistance would be much much appreciated.......

  5. #5
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Originally posted by syb_fkup

    So I suppose where I am now is between a rock and a hard place, I have managed to screw my database so that it just wont load (fortunatley only development ). So basically I am unaware of anyone who has successfuly shrunk the size of there transaction log in Sybase 11.9.* - I was hoping for an easy solution but its fsat looking like there is not one..

    Any assistance would be much much appreciated.......
    Any changes to sysusages will only be reflected in the internal dbtable memory structures after you perform a remap using the dbcc dbrepair otherwise you'll end up with those errors that you got even after a restart. Check the link that I provided earlier.

Posting Permissions

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