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.

Daily Checklist

• 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.

Weekly Checklist

• 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.

Monthly Checklist

• 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.

Quarterly Checklist

• 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.