10 Monthly Maintenance Tasks for SQL Server

Posted by .

checklist

Photo credit – NMK Photography

Are you doing what you must to ensure that your SQL Server environment is behaving as you, or your manager, expects that it is? Whether you are veteran, involuntary, or junior DBA, there are common things that we are required to do for our SQL Servers on a regular basis.  These tasks may seem like they are relatively low level items, but they are in fact critical in maintaining the health and security of our SQL Server instances.

This article includes the top 10 tasks that should be done each month in your SQL Server environment.  This list is maintained monthly, so come back each month for the current list of updates and items to take a look at.  You can find information on monthly changes to this list in the Monthly Checklist category.

1. Update Windows Server

As important as maintaining the SQL Server environment is maintaining the operating system that hosts SQL Server.  If the operating system is not up to date, then issues related to issues in the operating system could affect the performance and stability of the your environment.  Since the release of Windows Server 2012, Microsoft has shifted from releasing Service Packs for Windows Server to releasing Monthly Update Rollups, be sure to keep your server up to date with these patches.

2. Update SQL Server Instances

Is your SQL Server environment up-to-date?  Check each of your instances and review the most recent releases of SQL Server.  Make a plan to determine when the most recent updates will be applied.  Also, be aware that support for SQL Server releases do end at some point.  Support for the version of SQL Server that you are currently using may no longer be supported.

Beyond the items above, there are a few others things to pay attention to when checking the updates applied to your SQL Server instances.  Do any of the following apply to your environment?

  • SQL Server 2012 at all, there are a number of performance and stability related fixes that should be paid special attention too.  Check out Glenn Berry’s (Blog | @GlennAlanBerryblog post Performance and Stability Related Fixes in Post-SQL Server 2012 SP1 Builds for great information.
  • If you are using SQL Server Reporting Services, you need to check to see if the MS12-070 applies to your environment.  The bulletin identifies and patches a vulnerability related to cross-site-scripting.
  • If you are running SQL Server 2012 SP1 prior to Cumulative Update #2, there is a fairly important hotfix available that fixes an msiexec issue.  (KB2793634)
  • If you are running SQL Server 2008 SP3 CU 11, SQL Server 2008 R2 SP1 CU 12, or SQL Server 2008 R2 CU 6, you must verify that you have the correct versions running in your environment.  There was an issue with the initial release that has been resolved.  See the post Attention users running SQL Server 2008 & 2008 R2! by Aaron Bertrand (Blog | @AaronBertrand)

3. VALIDATE BACKUPS

Check everything involved in the backup process.  Are your backups executing as desired?  Are the monitoring jobs properly alerting to failures?  Have their been any unexpected failures?  Have backup duration times changed?

4. VALIDATE RECOVERY STRATEGY

You have your database backups, but do you have everything else required for recovery?  Is there detailed documentation on how to recovery all of your databases?  Have you practiced restoring at least one of your SQL Server databases from production in the last month?  If you don’t take the time to practice before there is a fire, you can’t be sure you know what to do.  Would you trust a fireman that never practiced?

5. Verify SQL Server and Windows Configuration

Should your server have hyper-threading enabled?  Is it?  Are there any trace flags that you think are running on the server?  Have you verified that?  What are your expected MAX DOP and Cost Threshold for Parallelism setting?  What about other SQL Server configuration settings?

6. CHECK Server Performance and Health

Were there unexpected outages or failures for the server in the last month?  Were there any unexpected items in the server error event log?  How does everything compare to the last baseline?

7. Check Database And Instance Performance AND Health

Check the performance statistics for your SQL Server instance and database(s).  Are there any variances on the performance counters off of the baseline?  Are spikes or valleys in performance statistics explainable?  Were there any unexpected items in your SQL Server logs?

8. REVIEW AND ADJUST BASELINES

During the performance and health reviews, were there any differences in baselines that justify a change in expected values?  Are all baselining processes running as expected?  Any new information needed as part of performance and health review?

9. Validate Capacity Plan

If you have a capacity plan in place for your environment, check to see that what you had planned for June matches the actuals.  Any threshold violations that may require adding capacity?

10. Submit Status Report

What do you need to get done before next month?  What did you get done this month?  After the other tasks, write this all down and send it to your manager.  Was there an outage in the last month?  Write a postmortem for the team.  Was there a team success? Write that down for management.  Be certain you are proving your value to the team each month by communicating with them.

Something Missing?

Is there something missing in this list that you think should be included?  Leave a comment and I’ll add it in for next month.