asked on

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

Hi,

Following on from this question :
https://www.experts-exchange.com/questions/24134156/SQL-Server-2005-multiple-log-files.html

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

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

reddal

ASKER

Hi,

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.