Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2006
    Posts
    12

    Unanswered: Reducing the log size

    Hi

    I increased the log size for a huge transaction earlier, how do I remove or reduce the log size from the device now?

    Please help... thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    You can't. You have to re-create the db.

    1 backup the db
    2 bcp out all tables
    3 ddlgen (or use a DBA or Data Modelling tool) out the entire schema for the db
    4 drop db
    5 create db with correct data and log sizes
    6 import entire schema except indexes
    7 bcp in all tables
    8 create indices

    Never ever increase the logsize for such a %$#! reason. Instead, break your batch jobs down into reasonable "transactions". The logsize should be a function of actual activity, not db size, and certainly not batch size.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  3. #3
    Join Date
    Nov 2006
    Posts
    12
    Hi

    Can I truncate the log size while doing a huge transaction? I have already set "trunc log on checkpoint", however my log will still be suspended and I have no alternatives except to increase the log size. Do you have any good advices for me?

    I am new to sybase and thanks a lot for the replies

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by mooks
    Can I truncate the log size while doing a huge transaction[
    Yes, but it will not remove any active transactions from the log as it is needed in case of rollback. Rather set "abort tran on log full" to terminate the offending transaction instead of casing a suspend.

    To reduce the log afterwords you can try this unsupported method

    To update/delete in smaller batches e.g.
    Code:
    declare @batchsize int
    set @batchsize=10000
    set rowcount @batchsize
    update bigtab set type='shrub' 
    where id between 2867127 and 1908917872
      and type='tree'
    while @@rowcount=@batchsize
    begin
      update bigtab set type='shrub' 
      where id between 2867127 and 1908917872
        and type='tree'
    end
    set rowcount 0
    ---- Or for delete
    declare @batchsize int
    set @batchsize=10000
    set rowcount @batchsize
    delete from bigtab where something='some value'
    while @@rowcount=@batchsize
    begin
      delete from bigtab where something='some value'
    end
    set rowcount 0

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Even if you truncate the log (manually or via 'trunc_log_on_checkpoint' db_option) in the midst of a large transaction, only the INACTIVE portion will get truncated. The problem is you have a large tran (actually larger than the log) which is in the ACTIVE portion of the log. The only real solution is to break up your massive single tran into multiple batches ala pdreyer above.

    The unsupported methods (both) are only for those experienced with the nuances of performing brain surgery on the server, and not recommended. Note the two gurus in the thread arguing about the considerations and the fallout of the two different methods.

    For a bit of explanation re logfile management, refer to http://www.dbforums.com/showthread.php?t=1604945.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  6. #6
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    The "this unsupported method" does not talk about lstart.

    The only thinjg that you have to worry about when dropping log fragments from sysusages is there shouldn't be any lstart gaps between the first and the last log fragment after the drop.

    So work your way backwards from the last contiguous fragment forsegmap =4 up until you've purged as much as you want.

    Once this is done, make sure that you do a dbcc dbrepair(<dbid>,remap)

  7. #7
    Join Date
    Nov 2006
    Posts
    12
    Hi All

    Thanks for all the valuable advices and tips

Posting Permissions

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