Here are few of the best practices one should follow for SQL Server Health Check.
- Conduct general health checks every 6 to 12 months.
- Create documentation and associate it with production and test SQL Servers to supplement operations and routine maintenance.
- Identify all the health issues during the health check and then develop a remediation plan. Don’t try to fix the issues during the health check because doing so muddles the results and slows down the health check process. And the fix for one issue might affect another.
- Retain all health check documentation and information for future use.
- Using SQL Server 2005 BPA is the most efficient way to scan the core components of SQL Server 2005.
- When using a locally attached storage, isolate the operating system from the database and transaction logs, preferably on separate RAID volumes.
- Allocate enough space on mission-critical databases and transaction logs to accommodate their growth.
- Manage antivirus scanning, excluding specific files within SQL Server.
- Use Windows Authentication.
- No one should log in as the Service Account to do administration.
Performance Tuning and Optimization has been my primary focus in my career for over 13 years and I do this for leaving. Please find here the details about how I can help you Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)