[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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

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
0
reddal
Asked:
reddal
1 Solution
 
reb73Commented:
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
0
 
reddalAuthor 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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now