The average DBA has to perform many routine checks on his or her servers. There will be daily checks, weekly checks and, probably, monthly checks. A short while ago, we were having quite a debate about what items would be on the DBA Daily checklist. Because we all like lists with ten items in them, we compiled a list that goes something like this:
- Connectivity: Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts.
- Backups: Check database and log backups, archiving and offsite storage.
- Events: check all database logs, application logs and system logs, Agent history, device logs, NIC logs etc. Investigate any job failures
- Processes: Check that all required processes on the server are running, including Replication
- Integrity: Perform all database and server integrity checks; look for objects that break rules
- Indexes: Check on indexes to see if they are being used, need re-creating, or if any are missing
- Volumetrics: Check resources on the server such as files sizes and disk space, and monitor growth
- Performance: Check application performance, and performance statistics, using the Perfmon tool; research and resolve any issues.
- Procedures: Check all Disaster Recovery Plans
- Security: Look for security policy violations
We thought we had a fairly exhaustive list, but then a Simple-Talk forum entry made us think again. Randyvol had posted in response to our DDL Trigger workbench:
"…last week some idiot turned a host of triggers off in our ERP system, causing a cascade of posting problems on dozens of orders before we caught the root cause…"
He wanted to know if there was some way of checking whether Triggers have been disabled. It may be unusual and obscure, but it makes perfect sense that on some production servers you will need to check to see if triggers have somehow been disabled. The consequences could be dire.
We added this check to our list but then began thinking: what else are we missing? There are so many different ways that SQL Server is used that there must be a host of other checks that could make all the difference.
As always, we'd like to hear what you think. Post your suggestions as a comment to this blog, and you'll go into the draw to receive one of five Simple-Talk gift bags!
Cheers,
Tony.