You are currently offline, waiting for your internet to reconnect

Separate database and transaction log files on different drives for optimal performance and disaster recovery

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SUMMARY
SQL Performance is typically enhanced if you place database and transaction log files on separate drives (preferably separate phyiscal drives). Read I/O for database files is tyipically random when reading database pages while I/O for the transaction log is typically sequential.

Furthermore, if the database is using FULL recovery the transaction log can still be used for recovery if the drive holding the database files is lost but the drive for the transaction log is still available.
MORE INFORMATION
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

Rule softwareRule titleRule descriptionProduct versions against which the rule is evaluated 
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)




Data and log files on the same volume





The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect if a database has its database and transaction log files on the same logical drive (the check is for the same drive letter). The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. If you run the BPA tool and encounter a Warning with the title of Engine -Data and log files on the same volume, then we have detected a database that has its database files and transaction log files on the same logical drive (based on a drive letter). The rule is exlcuded on SQL Express Editions, excluded for any database smaller than 5Gb in size (the thinking is that for smaller databases this is not a concern), and excluded for system databases master, model, tempdb, and msdb.SQL Server 2008
SQL Server 2008 R2






SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)



Data and log files on the same volume




The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect if a database has its database and transaction log files on the same logical drive (the check is for the same drive letter). If you run the BPA tool and encounter a Warning with the title of Engine -Data and log files on the same volume, then we have detected a database that has its database files and transaction log files on the same logical drive (based on a drive letter). The rule is exlcuded on SQL Express Editions, excluded for any database smaller than 5Gb in size (the thinking is that for smaller databases this is not a concern), and excluded for system databases master, model, tempdb, and msdb.SQL Server 2012 








Properties

Article ID: 2033523 - Last Review: 04/02/2012 22:57:00 - Revision: 2.0

Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Evaluation Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Express with Advanced Services, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Express, Microsoft SQL Server 2008 R2 Express with Advanced Services

  • KB2033523
Feedback
"https://c1.microsoft.com/c.gif?DI=4050&did=1&t=">