Home Forums SQL Server 2008 SQL Server 2008 - General Placing data file , log file and temp file in 1 drive or separate drive ? RE: Placing data file , log file and temp file in 1 drive or separate drive ?

  • The data and log files for any given db must always be on separate drives.  That is, that a single physical drive failure cannot destroy both data and log files for the same db.  Otherwise a single failure will destroy the whole db.

    However, there is really no need to put all logs on a single drive.  For example, for Db1, you could have data on driveA and log on driveB, and for Db2, you could have log on driveA and data on driveB.  But no db should have its data file(s) and log both on driveA or driveB only.

    Performance-wise, it can be best to spread tempdb across all drives, but if you have a dedicated drive available for tempdb, that can also work out well.

    If a log file runs out of space, the db becomes in effect read-only.  But if the log file is otherwise unavailable/damaged, the db will be unavailable also.  A db must have a log file.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.