Death to Maintenance Plans!!!

Let me start out by saying, Yes this is probably obvious to seasoned vets, but for rooks like me, its made a world of difference

I’ve found it hard to find topics to blog about. I recently attended SQLRally in Orlando, and met a lot of great people. Of which I spoke with a few in particular about how to get going with my blog, and was given great advice by Mr. Wes Brown (twitter|blog). “Blog about what you did at work that day,” he told me. It sounded so simple and almost obvious, Great advice Wes, thanks, and here we go!

Maintenance Plans, as an “accidental” DBA, maintenance plans (MP) were a pain in my rear and a mess. I stepped into a situation where not all databases were being backed up, let alone having maintenance run on them. Early on I struggled to understand what all went into a MP, until I found this e-book (free, BTW) by Brad McGehee. This book although simple, and very entry level, was perfect for me and for someone just getting into SQL Server. After reading this book from the 1st page to the last, I finally felt as if I had a good understanding of MPs.

Enter Ola Hallengren, this site blew me away! I ran across this site a while back, but at the time (before I read Brad McGehee’s book) it did me little good. But now I was armed with the knowledge of MP’s, now I want to make them better!

 

Here’s what I did, instead of using the MP wizard, etc, and having to waste time creating new MPs everytime a new database came online, I implemented Ola’s scripts going from about 50 individual MPs per Server (I know small potatoes) to one SQL Job with three steps…AMAZING!

(You must run the Store Procedure on Ola’s site prior to running these scripts, ‘MaintenanceSolution.sql’)

 

Step 1: Database Integrity Check- Basic integrity check

 

EXECUTE dbo.DatabaseIntegrityCheck

@Databases = ‘USER_DATABASES’

 

Step 2: Database Index Optimize- This script is pretty slick, because instead of rebuilding the entire db like we had to do with MP, this script allows us to get more granular.  Based on the level of fragmentation will determine how this script reacts.

 

EXECUTE dbo.IndexOptimize @Databases =’USER_DATABASES’,

@FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’,

@FragmentationMedium = ‘INDEX_REORGANIZE’,

@FragmentationLow = NULL,

@UpdateStatistics = ‘ALL’,

@OnlyModifiedStatistics = ‘Y’

 

Step 3: User Database Backup- This script allows us to create Full backups with Verify, and CheckSum in addition cleans up after itself (just like a good script should).

 

EXECUTE dbo.DatabaseBackup

@Databases = ‘USER_DATABASES’,

@Directory = ‘G:\Backups\’,

@BackupType = ‘FULL’,

@Verify = ‘Y’,

@CheckSum = ‘Y’,

@CleanupTime = 168

 

So in three simple scripts I was able to go from having way too many MPs to manage and a hot mess, to a very organized and precise Job, that accomplishes what I want, the way I want it to. Within the job, we still setup notifications upon failure, and scheduling. So if you are Accidental to SQL like me, and hate working with Maintenance Plans try this out, it will make your life a whole lot easier.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s