When I was performing DBA duties on a daily basis, I put together a list of things I would check on a (mostly) daily basis for each of the servers I managed. This was a manual process because I never found any tool that would do what I wanted done, the way I wanted it done. Many SQL Server monitoring tools are over complex, making them more trouble to use than they are worth.
The tasks I performed daily are very mundane and boring, but very important. This list included the following obvious tasks:
· Are all necessary backups being made that should be made (full, differential, log, etc)
· Have the backups been properly saved to tape or other archival storage
· Did all scheduled jobs (of any type) succeed
· Did any scheduled jobs conflict with each other enough to cause performance issues
· Are there any jobs that have run much longer than expected
· Are all databases being reindexed periodically
· Are all databases being checked for integrity periodically
· Are there any important errors in the SQL Server error log, and the OS event logs
· Does the server have at least 20% free disk space
· Do any of the system or user databases (or logs) have excess empty space
· Are old backups being removed as appropriate
· Identify significant blocking issues
· Identify deadlock issues
· Identify extreme performance counter activity
· Identify extreme hardware bottlenecks (cpu, memory, I/O, network)
· If a cluster, has there been a failover ( I manually check this using Cluster Administrator)
While I think the above tasks are obvious, when I check out the forums on this website, I see many questions related directly to the above. In other words, if DBAs had been doing the above, then the problems they are describing would not have happened in the first place. So maybe these tasks are not as obvious as I think they are.
If you have time, can you add to this list? I am sure I have left out some important tasks, but this is what I could think of as I sat down to write this. I would like to hear what other DBAs are doing along this same line of mundane, daily DBA work.