Routinely checking your systems is a critical part of your daily job as a DBA. Often times these simple checks can be enough to catch something bad before it becomes something really bad.
Depending on your environment, your DBA’s Checklist should include (but is not limited to):
1) Backups
- Check your backups by checking backup emails sent each night. If you receive a failure email, check the cause of it, run it again, and make sure they are still scheduled to run.
- Check the run time duration for the backups and compare with the average run time. If you see increase on run time, research the cause of it.
- Verify that all the databases are scheduled to backup, if any of them are not, determine the reason for it. If necessary run the backup for it and add it into the nightly schedule.
2) Disk Space
- Verify the free space on the servers, especially for backups folders and data folders. If you are seeing a significant increase in space being used, research the cause of it.
3) Job Failures
- Check for failed jobs on the servers. If a job has failed, resolve the issue and rerun the job if necessary.
4) System Checks
- Examine SQL logs on the servers. If you see any critical error, notify necessary parties and resolve the issue.
- Check Application logs on the servers. In case of a critical error, contact the necessary people to fix the issue.
- Check average of CPU usage of the last 24 hours. If you see significant increase in average usage, notify your network admin group for further investigation.
5) Performance
- Check Performance for all servers using the Performance Monitoring tool. If you find issues, research and resolve.
6) Connectivitiy
- Log into the critical applications and verify that they can connect and pull the data out of the database. Also verify that there are no performance issues.
**Anything specific to your environment, such as replication, mirroring, clustering, etc.
7) Replication
- Check replication on the servers by Replication – Local publications – Launch Replication Monitor in SQL Server Management Studio.
If you aren’t using any tools to monitor the things on this list, my suggestion would be to automate them using scripts and then emails by using DB Mail Agent Alerts. Run them nightly and email them to all DBA’s working on the database.
