In this blog post, I continue my series of SQL Server Health Checklists that I am developing for a new book I am writing. The focus of this checklist is database maintenance.
Database maintenance includes a defined set of proactive tasks that a DBA needs to perform on a periodic basis to help ensure that their databases perform optimally and maintain high availability. In many cases, the individual maintenance steps taken might not, by themselves, seem all that important, but the accumulative effect of performing database maintenance tasks can be huge.
The goal of this checklist is to help you document how you are currently maintaining your databases, and at the same time to help you establish database maintenance best practices. In most cases, you will answer most of the checklist items on a database-by-database basis.
As with all my previous checklists, it is in a spreadsheet form that allows you to modify it to meet your specific needs.
| How Do You Perform Database Maintenance | ||
| Custom T-SQL Script: | ||
| Custom PowerShell Script: | ||
| Maintenance Plan Wizard: | ||
| Third-Party Free Community T-SQL Script: | ||
| Third-Party Paid Vendor Tool: | ||
| Some Combination of the Above: | ||
| Are Your Scripts Managed Under Source Control: | ||
| Are Your Maintenance Scripts Consistent Throughout Your Instances: | ||
| Are Your Scripts/Jobs Documented, Where Are They Located, and Who Has Access to Them: | ||
| Physical File Fragmentation | Array #1 | Array #2 |
| List the Physical File Fragmentation for Every Array with MDF, LDF, BAK & TRN Files: | ||
| Is Physical File Fragmentation Monitored, If So, How Often: | ||
| Do You Regularly Perform Physical File Defragmentation, If So, How Often: | ||
| What Tool Do You Use to Perform Physical File Fragmentation: | ||
| Before Creating or Growing MDF or LDF Files, Do You Check for Physical Fragmentation First: | ||
| Are Your Physical File Fragmentation Procedures Documented, Where is it Located, and Who Has Access to It: | ||
| Database and Log File Management | Database #1 | Database #2 |
| Is Instant File Initialization Turned on for This SQL Server Instance: | ||
| List the Total Size (Database Plus Log) of Each Production & System Database: | ||
| List the Percentage Database Full for Each Production & System Database: | ||
| List the Percentage Log Full for Each Production Database: | ||
| List the Number of Virtual Log Files For Each Production Database Log File: | ||
| Will the Remaining Space for Your Production and System Databases Be Enough for the Upcoming Year: | ||
| Do You Manually Grow Your Database and Log Files Sizes Proactively to Minimize Autogrowth: | ||
| Do You Use Autogrowth to Manage the Size of Your Databases: | ||
| What Is the Autogrowth Setting for Each of Your Production and System Databases: | ||
| Do You Regularly Shrink Any of Your Databases or Log Files, If So, Why? | ||
| Do You Have a Standard Policy On Database and Log File Management So Databases are Created in a Standard Manner: | ||
| Is Your Standard Policy Documented, Where is the Documentation, and Who Has Access to It: | ||
| TEMPDB Maintenance | ||
| What is the Current Size of the TEMPDB Database File (When in Production Use): | ||
| List the Percentage Full of the TEMPDB Database File: | ||
| What is the Current Size of the TEMPDB Log File (When in Production Use): | ||
| List the Percentage Full of the TEMPDB Log File: | ||
| What is the Default Size of TEMPDB When SQL Server is Restarted: | ||
| Have You Pre-Sized TEMPDB Database and Log Files to Their "Typical" Production Sizes: | ||
| Is Autogrowth Turned on for TEMPDB: | ||
| Where are the TEMPDB MDF, NDF, and LDF Files Located: | ||
| How Many CPU Cores Does This SQL Server Instance Have Access Too: | ||
| Is the TEMPDB Database Divided Into Two or More Physical Files, If So, How Many: | ||
| What is the Current Size of the each of TEMPDB MDF, NDF, and LDF Files (Assuming Multiple Files): | ||
| Do You Regularly Monitor TEMPDB Performance to Identify Potential Performance Problems: | ||
| MSDB Maintenance | ||
| What is the Size of the MSDB Database: | ||
| Is SP_DELETE_BACKUPHISTORY Run Regularly Against MSDB, and If So, How Often: | ||
| Is SP_PURGE_JOBHISTORY Run Regularly Against MSDB, and If So, How Often: | ||
| IS SP_MAINTPLAN_DELETE_LOG Run Regularly Against MSDB, and If So, How Often: | ||
| Index Maintenance | Database #1 | Database #2 |
| Do You Regularly and Proactively Seek Out and Add Missing Indexes: | ||
| What Method(s) Do You Use to Identify and Add Missing Indexes: | ||
| Do You Regularly and Proactively Identify and Remove Unused Indexes: | ||
| What Method(s) Do You Use to Identify and Remove Unused Indexes: | ||
| Do You Regularly and Proactively Identify and Remove Duplicate Indexes: | ||
| What Method(s) Do You Use to Identify and Remove Duplicate Indexes: | ||
| Do You Regularly Identify and Remove Index Fragmentation: | ||
| What Method(s) Do You Use to Identify Index Fragmentation: | ||
| What Criteria Do You Use to Determine if an Index Needs to be Defragmented: | ||
| What Method(s) Do You Use to Defragment Indexes: | ||
| How Often Do You Run Jobs to Defragment Indexes, List by Database: | ||
| If Your Instance is Enterprise or Data Center Edition, Do You Perform Online Indexing: | ||
| How Often Do You Defragment the Indexes in Each Production Database: | ||
| List the Average Time It Takes to Perform Index Fragmentation Per Database: | ||
| Do You Use Filtered Indexes, If So, are They Documented (SQL Server 2008): | ||
| Statistics Maintenance | Database #1 | Database #2 |
| What Method(s) Do You Use to Maintain Index and Column Statistics: | ||
| How Often Do You Run Jobs to Maintain Index and Column Statistics: | ||
| If You Use the REBUILD Method to Defragment Indexes, Do You Also Update Statistics: | ||
| If you Use the REORGANIZE Method to Defragment Indexes, Do You Also Update Statistics: | ||
| If You Update Statistics Independent of Index Defragmentation, List the Average Time It Takes Per Database: | ||
| Do You Have AUTO_UPDATE_STATISTICS_ASYNC Turned On, If So, List Those Databases: | ||
| Do You Use Filtered Statistics, If So, are They Documented (SQL Server 2008): | ||
| Data Corruption Detection | Database #1 | Database #2 |
| List the Page Verify Setting for All of Your Production and System Databases: | ||
| Do You Regularly and Proactively Check for Potential Database Corruption: | ||
| What Method(s) Do You use to Identify Potential Database Corruption: | ||
| How Often Do Check for Potential Database Corruption in Each Production and System Database: | ||
| List the Average Time It Takes to Perform Database Corruption Detection Per Database: | ||
| Have You Documeted What You Would Do if Data Corruption is Detected: | ||
| Other | ||
| Do You Run SP_CYCLE_ERRORLOG Regularly, and If So, How Often: |
I would like your input on my SQL Server Database Maintenance Checklist. For example, I would like your input on:
- What is missing from the list? What other aspects of SQL Server database maintenance should be included? Keep in mind that I can’t include every possible variation.
- What on the list could be removed because it is not very important? Keep in mind that the list is designed to be generic, so there will be items on the list that will not be applicable to all SQL Server environments.
- Does my wording make sense, or should I change any of the wording so that it is more understandable or more accurate? Should I change some of the categories, or move some of the items to different categories?
Please add your comments below, and as I get feedback, I will update the checklist. Thanks!
