We help IT Professionals succeed at work.

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

reddal
reddal asked
on
Medium Priority
294 Views
Last Modified: 2012-05-06
Hi,

Following on from this question :
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24134156.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
Comment
Watch Question

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

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65702

http://support.microsoft.com/kb/328551

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.