SQL Server 2005 - multiple log files

Posted on 2009-02-11
Last Modified: 2012-05-06

I've got a fairly big SQL server 2005 database (approx 1tb) which is currently having some performance issues. It seems that the IO on the logfile is maxing out (the % Idle Time on the logfile disk is often zero).

The DB is setup with 2 filegroups on seperate raid arrays for data - and a single log file on a single drive (well a pair of drives with raid 1). This setup was recommended to me - but it seems that I'd get better performance if the logfile was on a stripped volume with more disks allowing more IO?

I'm thinking about creating a second logfile on an idependent volume which is not currently used by SQL server. This has slower disks but lots of them.

Would this be a good idea? Would SQL server use both logfiles?

thanks - reddal

Question by:reddal
    LVL 25

    Accepted Solution

    You could try, but the only way of forcing SQL server to use addtional log files is to not let the originall logfile autogrow which means SQL Server has to use the second one if it needs more space for the transaction log..

    Author Comment

    Note the recovery model for this database is SIMPLE - so the logfile is not building up.

    I tried to shrink the original logfile - but that didn't seem to do anything - so I  told it to shrink and reorganise pages etc - which did something and shrunk the logfile.

    I didn't shrink it to zero or restrict growth - but somehow SQL Server has now decided to use the new logfile - and wow - its much faster! Small sequential transactions are about 10 times faster!

    So was I given bad advice on the initial config? ie that a single (mirrored) drive would be optimal for the logfile?

    - reddal
    LVL 25

    Expert Comment

    I wouldn't go as far as saying you were given wrong/bad advise, its more a horses for courses thing really..

    How big (GB) was the log file when you had just one log file? It could well be that the new log file is pretty small in size currently and disk access is quicker, but wait till the second file grows and see if the improvement is maintained..


    Author Comment

    The original logfile was big (100gb), but was almost always 99% unused. ie I have SIMPLE recovery model.

    However I now have a new theory as to why it was slow before. The old logfile volume was on a specific pair of disks - but these were part of the same disk array as the data volumes - ie they shared the same raid card and interconnect. The new logfile is located on a different array with its own raid card and interconnect.

    Maybe beforehand the problem was nothing to do with disk io - and the bottleneck was just on the raid card or the SAS cable bandwidth? Hmm - I wish I knew more about how to diagnose that.

    Anyway - I just speeded up my DB several times so thanks for that. I'm still not clear how SQL server decides which logfile to use - I'll post a seperate question focused on that.

    thanks - reddal


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now