It’s only fitting that the very first of my favorite things be related to maintenance. SQL Server doesn’t take care of itself after all, and most seasoned DBAs agree that maintenance plans just suck. They behave strangely at times, for no logical reason at all. So when SQL Server awesome dude Ola Hallengren came up with his job-based maintenance solution, it was a total no brainer. One script to create jobs for index maintenance, integrity checks, backups and msdb cleanup? And all for free? Yes, please.
So to explain the title, one simply has to download and run the MaintnenaceSolution.sql file in SSMS from Ola’s website. The jobs are automatically created, and also the CommandLog table in the master database. That CommandLog table is totally cool because it will log every command that’s run via the Ola maintenance jobs, so you can have visibility of how long each command takes, as well as visibility on any errors.
To complete the total setup of the maintenance jobs, you have to do two things – specify a backup location and retention for the three backup jobs, and set schedules for all the jobs.
That’s it. Couldn’t be easier.
And it’s not just the setup speed that makes me love this maintenance solution. The index maintenance job uses smart indexing, which means it evaluates the level of fragmentation before deciding the operation. By default, if the fragmentation is less than 5% it does nothing, between 5-30% it does a reorganize command, and over 30% it does a rebuild. When run nightly, it is much more efficient than doing a straight rebuild.
Do you guys use this solution? Post a comment and let me know! 🙂
Thanks for sharing, and while not a DBA … I am in IT and it’s interesting to learn more. I work with Oracle … application development and ETL … and preparing for the journey into Cloud “Databasing”. 🙂
How cool! I hope you enjoy cloud databasing 🙂 I bet it’s different for Oracle in the cloud.