default location for ldf files

Question

default location for ldf files

tammy scolari

Valued Member

Points: 66
More actions
March 9, 2006 at 1:58 pm

#168617

Is there any reason why someone would save the mdf files in the default msql/data directory and then save the ldf files on a separate drive in a msql/log directory???
New to the job and that has me confused.
Thank you for any help.
Kindest Regards,
Tammy

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Nikolay Chernavsky Mr or Mrs. 500 Points: 573 More actions · Answer 1

One reason would be to gain some performance. I have separate volumes for mdf and ldf files consisting of multiple drives. LDF file is is the append mode most of the time. So, if you separate MDF from LDF you will get some performance boost depending on how many transactions your system executes per second. There are quite few articiles written on this site and on microsoft as well. Do search on transaction log and you will have a good night reading material.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Best practice is to put your TEMPDB on a separate drive and put your logs on another separate drive. Notice I said separate drive, putting them on a separate partition of a single drive does't improve anything.

So, if D is a separate drive, then that could be a good thing. If it's heavily used by other applications, it could be a bad thing.

-SQLBill

tammy scolari Valued Member Points: 66 More actions · Answer 3

Thank you for your reply. Further investigation of my configuration has left me with a bit of an empty feeling. This is what I have - one physical drive, RAID 5, two logical drives one storing mdf and the other holds the ldf files AND my bak and trn files....

What do you all believe would be the best case scenario to fix this.

Add one more physical drive to store my bak and trn files. Move the mdf and ldf files to one logical drive?

Add two more physical drives one to store my bak and trn files and one to store the ldf files.

How do I determine if I am actually getting a performance increase by storing my ldf files on a separate physical drive. I do not seem to have any additional third party tool to monitor the utilization on the databases. Is there some tech tip anyone could point me to for creating a baseline on the databases utilization and then collect a weeks worth of history so I can see what and when these databasese are used and how many writes are actually happening using performance monitor or enterprise management (standard)?

Kindest Regards,

Tammy