• ScottPletcher

    SSC Guru

    Points: 97739

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.