SQL Server DBA Checklist

I am looking for items that I should address on a daily basis on my SQL Servers. As such, what are the critical aspects of SQL Server that should I check on a daily basis? Should I perform additional checks on a weekly, monthly or yearly basis? How can I automate some of these tasks so I do not spend my whole day reviewing SQL Servers rather working on the latest and greatest technologies?

Solution

Depending on your environment dictates all of the items that should be reviewed on a daily basis as well as their criticality in your specific organization. Based on your environment, customize the list below to ensure it meets your needs:

Daily Checklist

• Backups - Check your backups to validate that they were successfully created per your process.
  • MSSQLTips.com Category: Backup and Recovery
  • SQL Server 2005 Exposed - Log File Viewer
  • Some of the hardware vendors write warnings to the Windows Event Log 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 2005 Exposed - Log File Viewer
  • Finding SQL Server Agent Job Failures
  • MSSQLTips.com Category: Performance Tuning
  • Standardized Error Handling and Centralized Logging
  • MSSQLTips.com Category: Service Broker

  Weekly or Monthly Checklist

  • Backup Verification (Comprehensive)- Verify your backups and test on a regular basis to ensure the overall process works as expected. What is meant by this is to:
    • Contact your off site tape vendor to obtain a tape
    • Validate that the tape goes to the correct office
    • Validate that the vendor delivers the correct tape
    • Validate that the vendor delivers the tape in the correct time period
    • Validate that the software version you use to perform the restore is compatible with the version from the tape
    • Validate that the tape does not have any restore errors
    • Validate that sufficient storage is available to move the backup to the needed SQL Server
    • Validate that the SQL Server versions are compatible to restore the database
    • Validate that no error messages are generated during the restore process
    • Validate that the database is accurately restored and the application will function properly
    • Maintenance Tasks: Automating the RESTORE VERIFYONLY Process
    • Verifying Backups with the RESTORE VERIFYONLY Statement
    • MSSQLTips.com Category: Service Packs / Patches
    • Easing the Capacity Planning Burden
    • MSSQLTips.com Category: Fragmentation
    • MSSQLTips.com Category: Maintenance
    • SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)

    Opportunities for Automation

    • Setup alerts for specific error levels or error messages that impact your SQL Servers in order to be notified automatically.
      • Database Backup and Restore Failure Notifications
      • One word of warning is to check your business critical Jobs on a regular basis just to be sure they are working properly. Nothing is worse than finding out a key process has been failing for days, weeks or months and the reason notifications have not been sent are due to an incorrect configuration, full mailbox, etc. It may be 30 minutes on a weekly basis that is time well spent.

      Next Steps

      • Based on your environment and your needs, build the daily, weekly and monthly checklist that is needed.
      • As a start it may be easier to manually check for specific business or data conditions, then as you build your scripts over time work towards assembling them for an automated process.
      • Do you have other aspects of SQL Server that you check on a regular basis? If so, enter your comments below.