Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    14

    Question Unanswered: where the transaction stored when used "no log option"

    hi all,
    I want to know where the transaction stored when we used in db-option
    "trunc with no log" ,so that we can recover the data.
    normally it is stored in
    "syslog"--->cache---->if commited---->disk
    so we can get updated data in when fire new querry,but if the option
    set then how it check that what is current data to be disply for the
    new querry.

    thanx to all !
    regards
    mindkits

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I think you misunderstand how the transaction log works.

    Once query is committed the log isn't used anymore as the data has been written to the actual table. So at that point the log can be truncated (either via 'trunc. log on checkpoint', or via an explicit DUMP TRANSACTION).

    Michael

  3. #3
    Join Date
    May 2006
    Posts
    14
    hi mpeppler,
    I think u r not understand my que. let me explain with ex.
    suppose I(dba) seating on system at remote location and end user eg. accountant
    at bank made 1000 transaction but not yet commited ,at this moment (dba) at remote site truncate the log , thus there is no log in syslog ,so how it work where it check to
    commit the transaction.
    thanx for reply!

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Uncommitted transactions will NOT be removed from the log when it is truncated.

    A truncate log will only remove the INACTIVE part of the log, i.e. up to (but not including) the oldest uncommitted transaction.

    Michael

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    A Bit of Explanation

    It has been a few days, and I have a few minutes ...
    - This is not oracle. I say this just so that you can understand that virtually all log space management in Sybase is automatic (and the bits aren't can be handled by scripts or thresholds). You never have to fiddle with tablespaces or syslogs, or figure out where the data is, so you need not seek to do so.
    - "trunc with no_log" is not a db_option, it is an option in the command "dump tran <dbname>", and which is only to be used in the worst emergency (truncate_only is slightly less worse; both deem the db unrecoverable)
    - you should not truncate the log (except in emergency), it is truncated when you dump (the Inactive portion of) it
    - normal use is "dump tran <dbname> to <file>"
    You also appear to think that the uncommitted transactions are "stored" in syslogs. For purposes of understanding (simplicity):
    - The data is ALWAYS in the table
    - any given page (addressable piece) in syslogs can be said to be in one of three categories: Active (uncommitted transactions); Inactive (committed transactions); and Free (not used at all). It is a circular file, so just think of it as 3 summary figures.
    - Update commands cause rows in the table to be locked and the uncommitted CHANGES are in syslogs/Active
    - Committing the tran writes the CHANGES from syslogs/Active to the table, and releases the space from syslogs/Active to syslogs/Inactive
    - Rollback releases the rows in the table, and releases the space from syslogs/Active to syslogs/Inactive
    - Commit/Rollback are transaction control commands
    - When "dump tran" (either manually or automatically by threshold sp) is executed, it writes the Inactive portion of syslogs to a named file (which you need for recovery) and clears syslogs/Inactive, releasing it to syslogs/Free. Unfortunately, this is known as "truncating the log", for historic reasons (think: truncate table, and syslogs is just another table), but "dumping the log" is probably more appropriate.
    - With few exceptions (see below) truncating only happens when the log is dumped.
    - "dump tran" is a db level administrative (space management) command; it is quite independent of: "update/insert/delete" (which uses up the log) and "commit/rollback" (which releases it).
    - Whether another user "sees" the data or not depends on the updating user committing the tran, NOT on any user dumping (truncating) the log. Do not try to read the log, it is for internal use only, just select from the table (and suffer the contention until transactions are complete).
    And another thing. Transaction size
    - 1000 rows/transactions are not transactions, they are obese and resource-hogging batches, that will cause deadlocks and log file problems.
    - Whether the users are separated by distance or sitting next to each other makes no difference: they are operating on (contentious (locked) rows in) a single table in a db in a server.
    - You cannot keep extending syslogs; you are much better off designing smaller transactions.
    - set abort_tran_on_log_full db_option, it eliminates the stress of trying to clear a choked syslogs, with the same effect as you would eventually, if you did so manually.
    Ensure your log is on a separate segment (to the data).

    If, and only if, it is a development db, you can set the db_option "trunc_log_on_chkpt", which allows you to avoid manual or threshold "dump tran <dbname> to <file>", but the db is then unrecoverable.

    For db recoverability, you need:
    a. One database dump file (created with "dump database <db> to <file>")
    b. plus the series of transaction dumps (created with dump tran <db> to <file>"), manually or by threshold, since (a)
    Note, any "lost" log dumps (truncation of syslogs without dumping: dump tran with truncate_only; dump tran with no_log; trun_log_on_chkpt db_option set) or any non-logged command (RTFM), deems the db unrecoverable, terminates the series (b), and you have to do (a) again.
    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
    May 2006
    Posts
    14
    Thanx Dereka,
    "THE BIT OF EXPLANATION"
    is really good one to understand by anyone

  7. #7
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    My pleasure. Thanks for the feedback.

Posting Permissions

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