The Background

Ola Hallengren’s SQL Server maintenance scripts have almost become an industry standard for management of you SQL Server scripts.

• Backup Database
• Index Rebuilding and Optimization
• Update statistics
• Check Db Health

All of these things can be done.

In all cases if the procedures are called with

@LogToTable = 'Y'

Then it writes to master.dbo.CommandLog

The issue

So now you want to monitor certain steps and validate how long a backup or an index rebuild took then you want to query the CommandLog table.

The solution

The following script provides some generic queries that monitor each type of job and shows you a snapshot of how long each took and success or failure.

use master
GO

-- Database Backup
select top 100
	l.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,
	l.StartTime,
	CONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, 
	Case When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber <> 0 Then 'ERROR' else 'Finished' END ENd as Status
from CommandLog l
Where l.CommandType in ('BACKUP_LOG', 'BACKUP_LOG', 'BACKUP_DATABASE')
Order By l.Id desc


-- INDEX 
select top 100
	l.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,
	l.StartTime,
	CONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, 
	Case When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber <> 0 Then 'ERROR' else 'Finished' END ENd as Status
from CommandLog l
Where l.CommandType in ('ALTER_INDEX')
Order By l.Id desc

-- STATS 
select top 100
	l.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,
	l.StartTime,
	CONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, 
	Case When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber <> 0 Then 'ERROR' else 'Finished' END ENd as Status
from CommandLog l
Where l.CommandType in ('UPDATE_STATISTICS')
Order By l.Id desc

-- CHECK DB 
select top 100
	l.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,
	l.StartTime,
	CONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, 
	Case When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber <> 0 Then 'ERROR' else 'Finished' END ENd as Status
from CommandLog l
Where l.CommandType in ('DBCC_CHECKDB')
Order By l.Id desc


-- STATS and Indexes
select top 100
	l.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,
	l.StartTime,
	CONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, 
	Case When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber <> 0 Then 'ERROR' else 'Finished' END ENd as Status
from CommandLog l
Where l.CommandType in ('UPDATE_STATISTICS', 'ALTER_INDEX')
Order By l.Id desc