Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2006-04-17 : 09:41:07
|
| I think I've read (somewhere) that placing LOG files on a different spindle than the databases "is a good thing". I don't see any option to allow for that during installation, so... it's done after the install?How? Detach... move the log... Attach?Sam |
|
|
bmanoj
Starting Member
13 Posts |
Posted - 2006-04-17 : 10:16:05
|
| I found one reference to place log file on diff physical disk at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx, under "Log File Placement" section.I guess the only option to move system dbs is after install as described in http://support.microsoft.com/kb/224071.HTHManoj |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-04-17 : 10:41:20
|
Good link. It had a minor error, the right link is:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx quote:
Log File PlacementHere are a few tips and best practices regarding the placement of your log files:Create the transaction log on a physically separate disk or RAID array. The transaction log file is written sequentially; therefore, using a separate, dedicated disk allows the disk heads to stay in place for the next write operation. For this reason, smaller systems will do well by using a single mirrored disk for the transaction log. A single mirrored physical disk should support up to approximately 1,000 transactions per second, depending on the speed of the disk itself. Systems requiring more than that should stripe the transaction log across a RAID 0+1 array for maximum performance. For highest bandwidth, the RAID controller on this array should have a (battery-backed) write-back cache to speed log writes.
Though it doesn't say how to do this... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 13:55:28
|
| You put your tlog on a different drive during the creation of the database. If the database is already created, then you can use either detach/attach or backup/restore (WITH MOVE option).Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-04-17 : 14:09:58
|
I don't think there is much to be gained by moving the log file for any of the system databases, except for tempdb.To move the tempdb log file, execute this command, restart SQL Server, and delete the old tempdb log file.alter database tempdbmodify file-- new location for tempdb log( name = templog , filename = 'L:\MSSQL\data\templog.ldf' ) Note:You should only use this method for tempdb. For user databases, you can detatch, copy the file, and reattach, or use backup and restore with MOVE. As I said before, just leave the other systems databases alone.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-17 : 14:30:19
|
| Agreed. We keep both files of the system databases on the same drive.Tara Kizeraka tduggan |
|
|
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-04-18 : 07:52:36
|
| I've moved the log files to a different spindle and have noticed some improvements.I generally have the data on one drive, logs on another and the tempdb on another.RegardsNThe revolution won't be televised! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-18 : 08:15:31
|
| "I've moved the log files to a different spindle"Ideally a different type of RAID too - Mirrored (Is that RAID-1??) for Logs and 0/1 or 10 (or whatever its called!) for data - RAID5 as a fallback if you don't have RAID10I've got a memory like a sieve today!Kristen |
|
|
|
|
|
|
|
|
Best Practices - File Layouts