SQL Server Performance

Slow disk allocation for transaction log

Discussion in 'ALL SQL SERVER QUESTIONS' started by Paul Rairdon, Sep 6, 2012.

  1. Paul Rairdon New Member

    Hello All,
    I have a Sql Server 2008 that takes a long time to grow a transaction log. It is currently taking an hour to grow the log by 500MB. This happens no matter what disk the data files are on. As a result I have had to turn off the weekly re-index on this server because if it has to grow the log it will bring the DB to its knees. Any help would be appricated. Thanks!

    Paul
  2. Luis Martin Moderator

    Welcome to the forums!.

    Couple of questions:
    1) What kind of recovery do you have?
    2) If full, how frequently do you backup data and transaction log?
    3) Are you rebuilding all database indexes or according fragmentation?
  3. Paul Rairdon New Member

    On the main database it is full recovery with transaction log backup every 15 minutes and fulls every night. The indexes are fully rebuildt once a week (not my design). I have created a dummy DB and get the same response when trying to grow it's transaction log no matter what drives I put the data files on it is the same thing.
  4. Luis Martin Moderator

    Rebuild indexes always means transaction log grow. My suggestion is to defrag indexes according fragmentation. You can find a lot of scripts about it.
    Anyway, if you have full recovery model you have to have plenty space in disks.
  5. ghemant Moderator

    Hi Paul,

    Is it taking this long everytime or just one time ? How are you growing TLog file? Manually ? what is the auto growth option set to % or fixed Mb ? what value ?
  6. Shehap MVP, MCTS, MCITP SQL Server

    Generally speaking , Index Rebuild jobs consume often lots of transaction log space particularly if tables of huge no of records are exists there regardless of its size (In other words if it contains BLOB or not )

    Therefore , its much preferable to conduct this while DB in bulk logged mode and run transaction log backups at short distances and change transaction log growth from percent ( Default ) to MB growth

    Moreover , I do agree with Luis suggestion to rebuild indexes according to fragmentation percent that if exceeded 30% ( Microsoft standard ) , it should be rebuild otherwise Reorganize can be adequate , thereby you can work the below script for a group of DBs exists on your production server:

    Kindly Let me know if nay further help is needed

    Attached Files:

  7. ghemant Moderator

    Apart from what Shehap and Luis has added, my point was - Auto Growth may trigger high I/O while expanding file size. For example, if you have a database with 400 gigs of size, and auto growth configured in %, say 10%. When this trigger it will get increased by 4 Gigs, and if this is the case with other DBs it will be additional burden to the I/O subsystem. Please refer http://www.sql-server-citation.com/2010/01/common-mistakes-in-sql-server-part-5.html for more details on this.

Share This Page