SQL Server 2005 - How does SQL Server decide which logfile to use when there are several?

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

Following on from this question :

I now have an SQL Server 2005 installation with 2 logfiles for a database with SIMPLE recovery model. I managed to get SQL server to use the new one - by shrinking the old one it seems. Subsequently I've also set the old one not to auto-grow.

However I wonder how SQL Server decides which logfile to use? It seems like it only uses one or the other - not both. I got a massive performance improvement by switching to the new logfile - so I'd like to understand how it decides so I can be sure it won't switch back.

thanks - reddal
Question by:reddal
    LVL 25

    Accepted Solution

    Haven't been able to dig up any concrete evidence relating to log file concurrency, the closest would be the following two links -

    Author Comment


    Well - I noticed bad performance was back - and sure enough SQL Server seemed to have decided to start using the old logfile again. No amount of shrinking seemed to help this time.

    I then tried the option to remove the old logfile - ie from the database properties dialog. However thats been going 5 minutes... oh dear - have I screwed it up?

    Ah - its come back - and all seems ok - I only have one logfile now in the new location and the performance is fast again.

    So the conclusion seems to be that theres no real sense to which logfile will be used when there are several. Also the tools for shrinking etc are a bit crap. However its worth perservering with.

    thanks - reddal

    p,s. for the benefit for anyone following a similar exercise - I also moved the logfile of the tempdb to the new location.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    732 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