Managing Data and Log Files

For better I/O, manage data and transaction log files from the disk to regulate their growth and to prevent poor performance. (Paul S. Randal, “SQL Server: Top Tips for Effective Database Maintenance,” Microsoft TechNet, https://technet.microsoft.com/en-us/library/2008.08.database.aspx [accessed August 28, 2018].)

To manage data and log files, ensure the following:

• Data and log files are on separate drives from each other and from all other files. The drive containing the data should contain only data (no logs), and the drive containing the log files should contain only log files (no data).
• Auto-growth is correctly configured.
• Instant file initialization is configured.
• Auto-shrink is not enabled and shrink is not part of any maintenance plans. If auto-shrink is enabled, disable it by using the following command:

  ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;