Moving System Log files
-
I just installed SQL 2005 Standard Edition on a Windows 2003 server with three disk drives. The harddrive, a Log drive, and a data drive. I ran a standard install nothing fancy. I installed sp1 as well.
I was out for a couple of weeks (medical) and when I returned I found that contractor had moved several of our prod databases over the the new server for testing. No big deal. I did however find that he had moved the system log files to the log drive off of the data drive where by default they had been installed. He said he read that it was the best way to set it up but couldn't find the article. I had changed the database settings to point any new database be created in the correct location but I am unable to find any article stating you should move your system log files to a separate disk. I am talking about all system database logs. Also moved were the Job logs, SQL logs, default backup file locations and Free Text Search. This required registry key edits and changed in the confuration manager.
Does anyone know of a good reason to do this? I have never moved system logs on other server I have set up. Am I missing a best practice or performance optimization trick? Do you know where I might find an article with a reason to do this?
Thank you.
-
Hi Crystal,
Putting the system database log files on a seperate drive isn't necessary. The system databases don't change much and as a result there is probably negligible I/O activity on these files compared with the user databases, which could be processing many transactions. This however, does not apply to the tempdb database. In an ideal situation it would be nice to have tempdb on its own drive.
Having said all of that, due to the negligible impact of the system logs it probably won't have any adverse effects having them on the log drive. Excluding the tempdb log file.
Now, where I would draw the line is putting job logs, sql logs and backup file locations on the same drive as the log drive - particularly the latter.
Logs are written to sequentially so you want to maintain performance by making sure the disk heads are writing sequentially (to whatever extent is possible). Putting all of these other files on the log drive will mean that the disk heads will have to move around a lot more. It's even worse when backing up log files because in this instance you're asking the same disk to read from the log file and write to the log backup file, which is not efficient.
Bottom line, from a performance point of view, you want to make your log drive work as fast as possible for the log files and cluttering the drive up with other files does not help.
From a redundancy point of view, it really makes no sense to backup your data and logs to the very same drive on which the logs (or data) exist. If you lost the log drive not only would you lose your logs but you'd lose your backups, which defeats the purpose of taking backups in the first place.
The only time you would do this is when you have no option because you don't have the disks. Given the low cost of disks this isn't much of an excuse anymore though. Certainly, if you can purchase an extra disk, I'd put that in the server and use that as a dedicated backup disk - which of course should be supplmented by tape backups (or someother backup that is taken offsite)
.Hope that helps,
-
Thank you.
-
There's not necessarily a one-size-fits-all answer, but I'll try to make some intelligent suggestions for a three-disk server.
Moving the transaction logs for the system databases to the log drive is not a bad idea. It won't make a big performance difference because of the low level of activity, but it won't hurt either.
I would put job logs and the SQL error log on the OS disk instead of either the data or log disks. I'm not sure where I'd put the full-text indexes, with only three drives there is no good choice. If I had to make a guess I think I would try to put them on the data drives instead, if they fit.
The backup files shouldn't be on any of the SQL drives, both for performance reasons and for redundancy. If you can't get another drive on your server try using a network share.
-
be aware that the sql server error log and agent log are prone to ntfs fragmentation and may soon present problems on any drive they exist, especially if disk space is short. To be truthful the sequential write propeties only really work where you have one dedicated drive per transaction log, as soon as you place multiple tran logs on a drive you introduce what in effect is random i/o - albeit in larger chunks!
I agree with Scott, it won't hurt keeping logs and data apart and in an ideal world I usually move system database logs onto the same array as the user databases - mainly for completeness. I usually have a seperate drive for the binaries for sql server which is where I leave the error logs , no data, log or backup files here.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply