SQL Server 2005 - multiple log files

Posted on 2009-02-11
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

Question by:reddal
  • 2
  • 2
LVL 25

Accepted Solution

reb73 earned 2000 total points
ID: 23613929
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..

Author Comment

ID: 23614435
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
LVL 25

Expert Comment

ID: 23614485
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..


Author Comment

ID: 23614657
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


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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

862 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