Routine SQL Server DBA Tasks

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.

Table of Contents

Related Posts