Saturday, September 26, 2009

Is it good to put data and log files together ?

This is a most common question asked ! The answer to this depends on the environment you work in.
What I mean by that is: If your environment is a heavy read / write then it is good to put data (.mdf) and log files (.ldf) on separate physical disks (preferably RAID). The reason for this is that when there are heavy inserts/updates/deletes, the way SQL server handles is that
1. It writes to the log files that it is going to change the data
2. It writes to the data files
3. It writes to the log file that it has changed the data i.e it commits the transaction.
This shows that it has to write both to the log and data files and the writes are random meaning it writes to the log file then jumps to data file and then again to the log file. This kills the performance making the writes much slower !

There might be good reasons to put data and log files on same disk/drive like
1. If your database is READ-ONLY i.e having more read activity than write like reporting or data warehouse.
2. If there is a lot of use of TEMP-DB only as compared to data / log file drives.
3. If you have a RAID-10 array, its good to put your data and log files on the same drive as RAID-10 is fast and have both mirroring and data striping functionality.

Although, to decide what should be implemented, a thorough research about the environment should be done and when the system is implemented using PERFMON to track the disk activity i.e disk read/writes helps !

Also to change the location of data and log file ALTER DATABASE can be used. **see my previous post how this can be done **

Hope this helps !

Thanks,
\\K
This blog is for SQL server and ArcSDE administration. Thanks for visiting !

Disclaimer: The contents provided on this blog are as is and the poster assumes no responsibility in case of any loss of data. Please test thoroughly the solution or codes provided in your environment.