September 7, 2005 at 8:01 am
During the installation of SQL Server, I'm able to specify where I want to put the data files. However, I have the extra step of moving the tlog files for the system databases after the installation (since I like to keep them separate). Is there no way to specify the desired location for system db tlog files upon installation?
September 7, 2005 at 8:59 am
This one you have to do it manually if you chose to have it seperate for the system database.
mom
September 7, 2005 at 9:00 am
Absolutely, you can specify this during setup if you don't accept the defaults and use the Custom option and change the default location for the log files (assuming this is SQL Server 2000). Alternatively, you can move the log files for the model DB and this setting will carry over to any new user DB's that are created after that.
My hovercraft is full of eels.
September 7, 2005 at 9:29 am
I'm not sure whether you can achieve this on install. You can customize location of binnaries and database files, but I don't know how to separate data files and log files.
IMHO this is not the best practice anyway, could you elaborate on what is the purpose of this setup?
September 7, 2005 at 10:58 am
Martin,
It IS best practice to separate your log and data files. Log files get LOTS of writes. Even Microsoft suggests putting the log files on separate disks.
I believe the best practice is:
data files on RAID 5
log files on RAID 1
I even have my TEMPDB on it's own disks.
-SQLBill
September 7, 2005 at 11:42 am
But there is no meaning throwing all (or anything more than one) log files on a single disk. You need separate disks for each log file. Is placing master's log file on a separate disk really that important?
Definitely agree on tempdb though.
September 7, 2005 at 1:38 pm
Say I've got a hundred databases, with all data files on one disk and all log files on another. How could I allocate a disk for every log file when there are so many?
September 7, 2005 at 6:31 pm
I agree that if you have got a hundred databases and need to optimize them all 'independently' you have got a problem, yes. But the fact is, to optimize transaction log throughput the log file needs to be placed on a separate disk. The reason is that the log file is written to in a serial way. If there is no other activity on the disk than writing to a single log file then the disk head will always be in the correct position to write and does not need to move around. With several log files on a disk the disk head would be moving between the current positions in the different log files, and most of the effect would be lost.
September 8, 2005 at 2:48 am
SQLBill, I didn't mean databases generally but solely system databases. Of course, it's best practice to separate data files and log files. We all know the reasons quite well.
On the other hand I can't imagine scenario when you could benefit from separating data from log files for master, msdb and model - workload on these databases is generally very low compared to user databases and the size is small. So the performance reason we can put aside and as for recoverability - you cannot do transaction log backups/restores on master anyway so if you lose master datafile, the log file itself is useless in my opinion contrary to the same situation with user db. So my best practice is to do full backups as often as needed and not to bring more complexity to sql server layout by separating system databases' data and logs. But I might be mistaken and overlooking something important.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply