asked on

SQL Server 2005 - multiple log files

Hi,

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

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

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

reb73

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

reddal

ASKER

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