Archive for the ‘SQL Server Maintenance Solution’ Category

The Amazing SQL Server Maintenance Solution Script

Similar to all SQL Server DBAs, over the past years, I have written my customized versions of the SQL Server maintenance jobs: Backup (full, differential, tlog), index maintenance, and update statistics. I have even posted and updated them in my blogs in the recent months. After close examinations and reviews of Ola Hallengren’s SQL Server Maintenance Solution over the past month, I will be using Ola’s “MaintenanceSolution” script in new server builds for setting SQL Server maintenance jobs whenever applicable or possible. I will be borrowing some ideas from it when needed.  It is amazing and it is free.

Ola’s SQL Server Maintenance Solution script are not new to the SQL Server community, many of us who follow SQL Server postings on the popular SQL Server websites or magazines have heard of or even read about the positive reviews from many industry experts.  Note that you can’t just blindly run the script and then call it a day. Similar to going to a fancy and expensive restaurant, the chefs can cook the best dishes for you on their good days. But, you still need to pick up the fork, spoon, or diner knife to enjoy the meal yourself. The following are the steps that I had tested in my development server (needless to say that’s a fancy term for my laptop):

  1. Download the script from http://ola.hallengren.com/ or here.
  2. Create a DBA utility database to store the stored procs and objects to be created by the script. I have called it DBAUtility in my development server.
  3. Open the MaintenanceSolution.sql script and modify the values for the @BackupDirectory , @OutputFileDirectory , and the @LogToTable arugments arguments.
    Arguments to Modify
  4. Execute the script again the DBAUtility database.
  5. Go to each of the SQL Server Agent jobs that were created and modify the values for the required arugments. Instead of SQLCMD, you can modify the jobs to run TSQL scripts. You can go to http://ola.hallengren.com/ for the references on the values for the arguments.
    SQL Server Maintenance Jobs
  6. Open the “Job Activity Monitor” window and then review each of the jobs that were just added and make sure their commands are correct and then add the schedules accordingly.

REFERENCES:

SQL Server Maintenance Solution
http://ola.hallengren.com/