SQL Server Blog

I’ve seen a few posts about daily checks recently and thought I’d chip in.

What do I check and a very brief explanation why I perform this check.

Backups
- Have they all succeeded?
Disk Space
- Do we have plenty of space available for normal operations during business hours?
Database Size
- Have we seen a major increase in size (I monitor this because we manage client databases)
Scheduled Jobs
- Have any failed? Or do any of the steps have warnings?
Event Viewer (application, security, server)
- Any errors at all relating to Security, SQL Server, the OS, hardware etc
SQL Error Log
- Check the logs to ensure there are no errors, events raised which may be of concern
Cluster Log
- Are there any errors, warnings?
Replication Logs
- Are all subscriptions in sync?
Maintenance Reports
- Automated rebuilds/reorganization of indexes and updating of stats. What was done? What wasn’t done?
Monitoring System eMails
- Any alerts over night from any of the database servers, web servers, application servers?
Overnight processing eMails
- Did all batch jobs run as expected?

Most of these are now automated processes. I capture the data on a daily basis in a seperate database for trends analysis & capacity management.

I have been asked by people why do I do all this? Quite simply, to ensure the stability of my production environment. Now, we do use a monitoring tool which alerts us to low disk space, errors in logs etc, but if we did not have this, then how would I ever know that my drives were running low on space or how would I know that an overnight report for Sales, for example, has not run?

It can be a mundane process, for sure, but I’ve been performing these kind of checks for long enough, it has just become routine for me in all of my jobs.

Do you backup production databases? If you do, fantastic. If you don’t, well, cross your fingers and just about everything else and hope that you don’t suffer a failure of any kind!

I read a number of posts in various forums where a database has become corrupt or there is no backup and you get a vast array of answers as to why there is no backup in place. In some cases, you may find that somebody has had the production databases dumped on them because they mentioned that they saw a SQL statement one time. While it’s still not really acceptable that there may be no backup in this scenario, it is understandable. However, if you are in this situation, then I would suggest you read Books on Line and ensure your data is backed up – don’t forget the restores too…A backup is no good if you can’t restore it!

For those full time DBA’s who have no backups, you have no excuse at all. Every DBA should be able to backup and restore databases in a variety of configurations and scenario’s.

I can’t stress enough the importance of backups.

Rant over! I’ll actually put together a series of posts to walk through backups and restores and the various configurations and scenarios.