Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: Transaction log configurations

    Hello, everyone:

    How to configure transaction log file size, transactional backup interval and log shipping intereval, exactly? Someone told me it depends on the transaction speed. Is it correct? I have an average transaction speed about 10/min., one day, on SQL 2K/W2K. How should set above issues?

    Thanks a lot.

    ZYT

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i've never really seen a formula for this but microsoft suggests that for a moderately transactional database? your transaction log should be sized from 10 to 25% of the databse's size --initially.
    the following parameters are what you should start with when considering Tlog sizing.

    1. the average size of your transactions.
    2. the frequency of transactions (the avg transactions per a given time period.)
    3. how often you perform log backups

    so you are on the right track... you just need to find a blend of the parameters that you are comfortable with.

  3. #3
    Join Date
    Mar 2003
    Posts
    223
    Thanks a lot Ruprect. Could you give me an idea about "the average size of transaction ". How to predict or measure that?

    ZYT


    Originally posted by Ruprect
    i've never really seen a formula for this but microsoft suggests that for a moderately transactional database? your transaction log should be sized from 10 to 25% of the databse's size --initially.
    the following parameters are what you should start with when considering Tlog sizing.

    1. the average size of your transactions.
    2. the frequency of transactions (the avg transactions per a given time period.)
    3. how often you perform log backups

    so you are on the right track... you just need to find a blend of the parameters that you are comfortable with.

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    sure
    when users create a session they perform what is refered to as a shopping cart transaction" (every "" transaction "" is actually made up of smaller transactions)
    this is all of the inserts updates and deletes that a user performs during an average session. so while in fact you may be buying a book from amazon you are updating the products table (instock), customer table(credit info), will call table(processed shipped received etc)
    you can see that one ""xact"" can actually be many small xacts during a session.. so, calculate the row size per each of these changes and the values that are being entered, and estimate a total size for a average xact. you will also need to create a table baseline to compare against, and work with sp_spaceused and dbcc sqlperf to monitor logspace and size....
    the tlog is dynamic and there is no one size fits all. you will have to monitor this process and collect data

    the basic rules for the log process are as follows:

    1. Each log should be on it's own disk to aleviate contention.
    2. always set a max size for the log, dont alow it to grow to disk.
    3. watch long running xacts and loading of data into indexed tables.
    4. always perform log backups to protect AND maintain the log
    5. try not to truncate the log,or do it as little as possible (it makes it loopy)

Posting Permissions

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