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)
Truely said. The health checkup is a big exercise and should involve all possible angles to indentify the performance issues. Some most common mistakes hit the performance in long run and we never realise that.
Experts can point to those aeras during the Health Checkup process.
suggest me a good book for sql server2005
Pinal i found the best tool for health check is SQLDIAG, but still lots of festures i didnt get. I would suggest you to write on SQLDIAG formulas and how and what do we found while SQLDIAG report,
I havnt found yet .
It would be great if you can help me out in that ,any article or book on SQLDIAG.
Hi I would like to know what would I need to look for when I perform a SQL Server 2005 database assessment. What would I need to look for when performing a database monthly healthcheck.
Is there a better tool? Or can we create a set of scripts for monitoring this?
Some SQL SERVER good books:
MS SQL SERVER 2005 Administrator’s Companion &
what is database health checkups ?
Please help me to identify which SQL patches if any are installed on Server
I was wondering if maybe there is an sql command used to monitor the server health or do you have to check for the health manually?