House of Brick Principal Architect
Every once in a while I get asked about checklists and routine tasks that DBAs should be performing. I have a set routine for the various activities around day to day management.
First, I have a routine for the servers that I manage. There are tasks that I perform on a set time period, and just about all of these items are collected automatically and then a report delivery mechanism (usually SSRS) has my daily/weekly/etc. report in my inbox when I arrive at work. I review the reports over my morning cup of tea and see if I can catch anything that might normally catch me off guard later in the day or week.
- Check the previous night’s SQL Server database and transaction log backups and SQL Server Agent jobs for errors.
- Check all databases to make sure all are up and not marked as suspect.Check previous night’s DBCC CHECKDB for errors.
- Check SQL Server and Windows application and system event log entries for warnings and errors and determine if any entries warrant further investigation.
- Check mirroring status for all databases being mirrored.Check for service status for all Windows services that are required for operation (i.e. Windows Full-Text Indexing, Search Server, etc.)
- Look for any security policy violations.Look for resources on the server, such as file sizes and disk space, and audit growth for long-term projections.Check system performance levels against established baselines.
- Use long-running queries or tasks, Perfmon, etc. to generate the data.Double check that no configuration changes have been made on the server, and if so, document and investigate.
- Ensure that all data replication tasks are operating normally.Record and compare last day of CPU activity with known baseline.
- Check for full system backup status.
- Verify that the MSDB database is being backed up.
- Verify that index and statistic maintenance has completed.
- Perform sample restores of database backups on preproduction servers to spot-check backup set integrity.
- Examine SQL Server wait statistics to see if new ‘pain points’ have become large points of contention within SQL Server.
- Audit job execution time for dramatic variances against baseline runtimes.
- Investigate databases for objects that violate established rules.
- Verify that SQL Server is sending database mail properly.
- Compare SQL Server run book specifications against current configuration. Update run book if necessary.
- Perform system maintenance, such as disk defragmentation, Windows updates, SQL Server cumulative updates
- Verify that data access speed is running at normal levels.
- Perform full system growth projection update as part of normal capacity management.
- Perform a full index analysis for all databases. Determine unused indexes and disable if appropriate. Determine missing indexes and investigate process to update or add indexes to better cover data usage.
- Review systems for tuning opportunities. Common tuning indicators include buffer cache hit ratio, page life expectancy, I/O and network performance, long running queries, average CPU utilization, etc. Perform tuning as necessary.
- Perform full test of disaster recovery plan.
- Perform full system restore against a virtual machine so that all core systems and services can be brought up in isolation and tested for integrity.