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

Article ID: 2033523 - View products that this article applies to.
Expand all | Collapse all

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:

Collapse this tableExpand this 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: April 2, 2012 - Revision: 2.0
APPLIES TO
  • 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
Keywords: 
KB2033523

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com