SQL Server Performance

Large Transaction log on DB with Simple Recovery Model

Discussion in 'Performance Tuning for DBAs' started by etamminga, Feb 22, 2010.

  1. etamminga New Member

    We have a 250+ GB database which is set to simple recovery model. The transaction log file still is 60GB in size and we experience slow performance on this database.
    What could cause this large transaction log (with 90+ % utilization) with this recovery model? Could this impact our performance?
    SQL 2000 on a Windows 2000 server with 8GB Memory, no other apps/tasks running on the same machine.
  2. satya Moderator

    Welcome to the forums.
    There is no hardcore rule that size of transaction log contributes the slowdown of performance,unless you see such behaviour during the database backup (as you say DB is in SIMPLE recovery model so no transaction log backup).
    On this site you can have a look at the articles (last 8 years), http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx & http://www.sql-server-performance.com/tips/sql_server_performance_monitor_coutners_p1.aspx links for instance can help you.
  3. dineshasanka Moderator

    You might have change recovery model to simple from full. In that case, log size will not truncate.
    But, in a production server, your recovery model should be full with log backups are enable.
  4. preethi Member

    [quote user="dineshasanka"]You might have change recovery model to simple from full. In that case, log size will not truncate. [/quote]
    Remember to take a full backup after changing the recovery model. Otherwise, the effect will be the same.
  5. satya Moderator

    If the transaction size of very big then there is no guarantee that either FULL or SIMPLE recovery models will not be any use, so the key is to ensure there are no BIG transactions -such as deleting or inserting millions or rows to the table, if there is a process to upload millions of rows then BULK LOGGED recover helps too.

Share This Page