— By Lori Brown @SQLSupahStah
What are the critical aspects of SQL Server that a DBA should check on a daily basis? How about on a weekly or monthly basis?
Check your backups (Full, Differential, Transaction Logs) to validate that they were successfully created per your company’s policies.
Review the nightly or early morning processes for success or failures and troubleshoot as needed.
Review the status of Cluster nodes, Log Shipping, Replication, AlwaysOn or Mirroring on both the primary and secondary instances and troubleshoot as needed. If a failover occurs, investigate root causes immediately.
SQL Server Error Log
Review the SQL Server error log for any unexpected errors, warnings of bad performance or security issues.
Windows Event Logs
Review the Event Logs at a minimum to find out if any Windows or hardware related errors or warnings are being written. Most hardware vendors write warnings to the Event Logs when they anticipate an error is going to occur, so this gives you the opportunity to be proactive and correct the problem during a scheduled down time, rather than having a mid-day emergency.
SQL Server Agent Jobs:
Review for failed SQL Server Agent Jobs.
Validate you have sufficient storage on your drives to support your databases, backups, batch processes, etc. in the short term. Track your file growth and project when you will need to add space as drives fill up.
Weekly or Monthly Checklist
Verify your backups and test on a regular basis to ensure the overall process works as expected. Validate that no error messages are generated during the restore process. At bare minimum use the RESTORE VERIFYONLY statement to verify that backup files are usable.
Windows, SQL Server or Application Updates:
Check for service packs/patches that need to be installed on your SQL Server from hardware, OS, SQL or application perspectives.
Track how often tables are defragmented. Review the fragmentation info for your tables to determine if the fill factor is too high. Be aware of forwarded records on heap tables and be prepared to remove them.
Perform database maintenance on a daily, weekly or monthly basis. This includes index rebuilds, reorgs, statistics updates and integrity checks.
Track processor, memory, disk performance and free space, network and key SQL Server performance and log performance counters. Review these counters to determine if there are any aspects of the hardware that may have reached capacity. Be very aware of Page Life Expectancy to detect memory pressure. Check file sizes of any database with multiple files in a filegroup including tempdb to make sure that all are the same size.
Remove unneeded logins and users for individuals that have left the organization, had a change in position, etc.
Review the SQL and database configurations for changes. Make sure that all configuration options set are in alignment with your company’s policies or best practices.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!