Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Reduce the size of the log file

    Hi all,

    I have created a new device in sybase and set it as a transaction log accidentially. Actually, I want to set it as a datafile instead. As I have set the size is so large, is there any method I can reduce the size or changing it from log to a datafile. Besides, what is the side effect if the transaction log is too large?

    Thanks.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    To change it to a data device
    drop the logsegment and add the default segment to the device

    No way to remove it that is supported by Sybase
    Make sure you have full backups including master if you want to attempt removing the last device added as log. (play on test server)
    I've done it before and took some experimenting.
    If I remember the steps correct.
    Remove all segments from the last device (sp_dropsegment)
    select logptr from sysdatabases
    do dummy transactions until the logptr is not on the last device added
    as can be seen from sysusages
    logptr between lstart and lstart+size-1
    delete the database's last entry from sysusages
    dbcc dbrepair (<dbname>, remap)
    dump the database, recreate and load the database to ensure everything work as expected (i.e. database does load the now smaller size)

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Is it a official method to change the logdevice to a datadevice? It seems so risky. And what is the side effect if the log file is too large? will it cause a rollback problem? Thanks.

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No problem changing log to data.
    A large log can lead to a long wait for a rollback if you had a misbehaving tran

Posting Permissions

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