{"id":2074,"date":"2024-08-08T18:40:23","date_gmt":"2024-08-08T08:40:23","guid":{"rendered":"https:\/\/ntsblog.homedev.com.au\/?p=2074"},"modified":"2025-06-19T00:42:50","modified_gmt":"2025-06-18T14:42:50","slug":"monitor-ola-hallengren-sql-maintenance-commands","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2024\/08\/08\/monitor-ola-hallengren-sql-maintenance-commands\/","title":{"rendered":"Monitor Ola Hallengren SQL Maintenance Commands"},"content":{"rendered":"<div id=\"ntsbl-1200088993\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div>\n<h2 class=\"wp-block-heading\">The Background<\/h2>\n\n\n\n<p>Ola Hallengren&#8217;s SQL Server maintenance scripts have almost become an industry standard for management of you SQL Server scripts.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Backup Database<\/li>\n\n\n\n<li>Index Rebuilding and Optimization<\/li>\n\n\n\n<li>Update statistics<\/li>\n\n\n\n<li>Check Db Health<\/li>\n<\/ul>\n\n\n\n<p>All of these things can be done.<\/p>\n\n\n\n<p>In all cases if the procedures are called with <\/p>\n\n\n\n<p><code>@LogToTable = 'Y'<\/code><\/p>\n\n\n\n<p>Then it writes to master.dbo.CommandLog<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The issue<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The solution<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<pre title=\"Monitor CommandLog\" class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql line-numbers\">use master\nGO\n\n-- Database Backup\nselect top 100\n\tl.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,\n\tl.StartTime,\n\tCONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, \n\tCase When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber &lt;> 0 Then 'ERROR' else 'Finished' END ENd as Status\nfrom CommandLog l\nWhere l.CommandType in ('BACKUP_LOG', 'BACKUP_LOG', 'BACKUP_DATABASE', 'RESTORE_VERIFYONLY')\nOrder By l.Id desc\n\n\n-- INDEX \nselect top 100\n\tl.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,\n\tl.StartTime,\n\tCONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, \n\tCase When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber &lt;> 0 Then 'ERROR' else 'Finished' END ENd as Status\nfrom CommandLog l\nWhere l.CommandType in ('ALTER_INDEX')\nOrder By l.Id desc\n\n-- STATS \nselect top 100\n\tl.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,\n\tl.StartTime,\n\tCONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, \n\tCase When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber &lt;> 0 Then 'ERROR' else 'Finished' END ENd as Status\nfrom CommandLog l\nWhere l.CommandType in ('UPDATE_STATISTICS')\nOrder By l.Id desc\n\n-- CHECK DB \nselect top 100\n\tl.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,\n\tl.StartTime,\n\tCONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, \n\tCase When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber &lt;> 0 Then 'ERROR' else 'Finished' END ENd as Status\nfrom CommandLog l\nWhere l.CommandType in ('DBCC_CHECKDB')\nOrder By l.Id desc\n\n\n-- STATS and Indexes\nselect top 100\n\tl.Id, l.DatabaseName, l.ObjectName,  l.CommandType, l.IndexName, l.StatisticsName,\n\tl.StartTime,\n\tCONVERT(TIME, Convert(datetime,l.EndTime) - Convert(datetime,l.StartTime)) as Duration, \n\tCase When l.EndTime is NULL then 'RUNNING' Else Case When l.ErrorNumber &lt;> 0 Then 'ERROR' else 'Finished' END ENd as Status\nfrom CommandLog l\nWhere l.CommandType in ('UPDATE_STATISTICS', 'ALTER_INDEX')\nOrder By l.Id desc<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Monitor Ola Hallengren&#8217;s  CommandLog tables<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2074","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/2074","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=2074"}],"version-history":[{"count":1,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/2074\/revisions"}],"predecessor-version":[{"id":2176,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/2074\/revisions\/2176"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=2074"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=2074"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=2074"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}