Thursday, September 25, 2014

Ola Hallengren Command Log History Scripts

If you are like me and use the Ola Hallengren set of free scripts to help manage backups, checkdbs, and index maintenance operations then you may find yourself needing to dive into the Command Log history to calculate the duration of some of your common operations. See below for some scripts I wrote to query the data in the CommandLog.

Command Type Duration by Days of the week

I was trying to track down which indexes were being rebuilt on what days. The Index job was taking longer on some days then others and I was looking for a trend. This stored procedure will show you all the ALTER_INDEX commands (or whatever other commands you want to see) by day of week. This will help you get an idea of what indexes are reorganized or rebuilt on what days.:

This will show you the duration for each day of the week. If you pass the optional DaysBack parameter as 7 it will show you the history for the past 7 days broken down by day of week.


Select statement to show all the indexes that were included in maintenance on a specific date. I wanted to see what indexes were modified today so I used the script below.

CommandType Count Script

I needed a quick SELECT to show the number of time an index had been reorganized or rebuilt.


  1. Replies
    1. You blocking javascript or something? I see them fine in Chrome or IE. I am using some JS to do the code blocks.

  2. Okay, that particular site is blocked at my work (