Question
I am just starting as a DBA in a new company. We are running a single SQL server. We have about 15 databases, the largest being 2 GBs. During peak hours and weekends, there are several hits on the server that affect performance. I found out that the data files and log files are on a single drive, which may be causing disk contention. My question is how do I move already existing log files to a different drive, or how can I separate data files and log files onto different drives? We are not using RAID. Are there any other methods to improve performance?
Answer
The first thing I would recommend would be to move to a RAID array, preferably a RAID 10 array. This would not only offer a performance boost, but would provide very necessary data redundancy.
If you try the above, and disk contention is still a problem, or if you can’t move to a RAID array, then you can of course move the log files to a separate disk. Doing so can significantly reduce disk contention and boost SQL Server’s performance. Keep in mind that the new disk should be devoted exclusively to log files so that they can be accessed sequentially, which is the most efficient way to access log files.
One of the easiest ways to move log files from one disk to another is to first detach the databases (and their logs) using sp_detach_db, the copy the log files to an appropriate location on a different drive, and then reattach the database files and log files using sp_attach_db. When you reattach the database, you can specify where the data and log files reside.
If you are not familiar with these two commands, you can find out more about them from the SQL Server Books Online.
]]>