Routine SQL Server DBA Tasks

Share on linkedin
Share on twitter
Share on facebook

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

House of Brick focuses on cloud adoption & secure management for enterprise applications and databases