THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

October Update to Rules-Driven Maintenance

Happy Fall! It’s a beautiful October here in Minneapolis / Saint Paul. In preparation for my home town SQL Saturday this weekend, as well as the PASS Summit, I offer an update to the Rules-Driven Maintenance code I originally published back in August 2012. It’s hard to believe this thing is now more than two years old – it’s been an incredible help as the number of databases and instance my team manages has grown.

One enhancement with this update is the ability to set overrides for both Index and Statistics maintenance on a per-object basis, for example to use full scan on specific tables, or set custom thresholds or exclusions for index maintenance.

Statisics maintenance has gotten a lot smarter, kicking sp_updatestats to the curb in favor of a more intelligent adaptive statistics update based on these findings.

The basic concepts for this solution remain the same: preferences, which act as rules, are stored in an administrative database locally on each SQL Server, and SQL Agent jobs look at those preferences to decide how to manage all the databases on the instance. This structure allows hundreds or even thousands of SQL Server databases to be maintained and backed up using a single code base, with just five jobs.

A detailed description and instructions are available back in the original post.

As with the other version updates, the code here is cumulative, so it can be installed from scratch or over the top of a previous version.

This software is free under the GNU public license.

Published Wednesday, October 09, 2013 5:19 PM by merrillaldrich

Attachment(s): RulesMaintenanceV1_3.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Merrill Aldrich : Rules-Driven Maintenance said:

October 9, 2013 7:24 PM
 

Merrill Aldrich said:

Back in August I posted a first version of a rules-driven solution for backups, index and statistics

October 9, 2013 7:26 PM
 

Merrill Aldrich said:

This month I have a minor update to the Rules-Driven Maintenance code I originally posted back in August

October 9, 2013 7:27 PM
 

Oleg said:

Seems there are bugs in Powershell Script.

There are variable named $targetAdminDB, and by default its $null.

But later this variable is not used, I think replacing $targetAdminDB with $adminDB  in script is better choice.

Thanks,

Oleg (zhunya@gmail.com)

January 30, 2014 9:33 AM
 

merrillaldrich said:

You're right, Oleg - thank you for that tip. The intent was just as you say and I'll correct this in the next version.

February 19, 2014 11:56 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement