We help IT Professionals succeed at work.

SQL Server 2005 - multiple log files

reddal asked
Medium Priority
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

Watch Question

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..

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

Ask the Experts


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

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..


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

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.


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.