Database Maintenance

I think back, and I honestly can't even count how many different tactics I have used for database maintenance.  I am talking about the MUST HAVE stuff.  The Backups, the DBCC's... the stuff you have to maintain on a daily, or near-daily basis, in order to guarantee the integrity, stability and recoverability of your SQL Server inventory.  (1 server, or 100 servers)

For consistency, and ease of administration, I have always created one job, using one code base, to be used across all of my servers.  Backups, DBCC's, Index reorg -- all of it.  Many times, however, my approach was lacking.  Maybe it was good logic for OLTP, but it didn't suit the combined OLAP/OLTP environment.  Or, maybe it didn't suit the partitioned data model.  I put in many different bandaids and workarounds... until I found this:

This logic was suggested by one of my MSFT Premier Field Engineers (thank you, @SQLGoddess!), when I was struggling to identify why only ONE instance was performing horribly during the nightly maintenance.  The fix to that problem was another story, but I did begin testing this solution.  And, I ended up pushing it across the farm.  That particular role was a very aggressive HFT (high frequency trading) environment, with several different data models.  One of the beautiful things about this solution is that it is extremely manageable, and customizable.  It's not one chunk of code that does one thing.  You have different options, for different requirements. Maybe exclude your read-only filegroups, update statistics only when data changes, or even compress your backups.  I don't want speak in error, so it's best if you visit the above link.  I will, however, post two fully functional SQL Server Agent jobs for System & User database maintenance, with Integrity check, Index Optimization and Backups within each.  You'll need to change the directory structure for log and backup output, and your recipients for success/failure notifications.  Other than that, it is perfectly usable, as-is.

I encourage you first to review Mr. Hallengren's site.  Take a look at the Setup, Getting Started, and the Error Scenarios, and please try this in your dev bed first.

Oh yes, one other thing -- in each of the @command lines you will see reference to 'dbawork'.  That is just a working database that I always use to centrally manage my logic, and keep it out of the system and user databases.