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

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

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

0
 
reddalAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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