{"id":1028,"date":"2016-01-05T14:50:44","date_gmt":"2016-01-05T22:50:44","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1028"},"modified":"2018-11-13T10:52:22","modified_gmt":"2018-11-13T18:52:22","slug":"sql-server-diagnostic-information-queries-detailed-day-5","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 5"},"content":{"rendered":"

For Day 5 of the series, we have Query #9<\/strong>, which is SQL Server Agent Alerts. This query looks at the sysalerts<\/a> table on the msdb system database. Query #9 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> name, event_source, message_id, severity, [enabled], has_notification, <\/pre>\n
<\/p>\n
   3:<\/span>        delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> msdb.dbo.sysalerts WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   5:<\/span> ORDER<\/span> BY<\/span> name OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span> -- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- Read<\/span> more about Agent Alerts here: https:\/\/www.sqlskills.com\/blogs\/glenn\/creating-sql<\/span>-server-agent-alerts-for<\/span>-critical-errors\/<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #9 SQL Server Agent Alerts<\/strong><\/p>\n
In my experience, probably 90% of the SQL Server instances that I look at don\u2019t have any SQL Server Agent Alerts<\/a> in place, often because many DBAs are not sure what they are or why they are useful.<\/p>\n
Despite the name, SQL Server Agent Alerts have very little to do with SQL Server Agent jobs. I have run into quite a few DBAs that mistakenly assume that SQL Server Agent Alerts are what occur when SQL Server Agent jobs fail. This is not the case. These are actually special alerts that you can create to detect when certain errors or other conditions occur, such as SQL Server performance condition alerts or WMI event alerts. <\/p>\n
When one of these alerts is triggered, you can have SQL Server Agent run a SQL Server Agent job and\/or notify a SQL Server operator via e-mail or pager\/smart phone. This makes it much more likely that someone will notice a problem sooner rather than later. This blog post<\/a> explains SQL Server Agent Alerts in more detail, and it also has a link to where you can download a T-SQL script that I wrote that you can use to create some very useful Agent Alerts on your server.<\/p>\n
<\/strong> <\/p>\n
Query #10<\/strong> is Windows Info. This query retrieves information from the sys.dm_os_windows_info<\/a> dynamic management view about the operating system that you are running your instance of SQL Server on. Query #10 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Windows information (Query 10) (Windows Info)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> windows_release, windows_service_pack_level, <\/pre>\n
<\/p>\n
   3:<\/span>        windows_sku, os_language_version<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_os_windows_info WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   5:<\/span>  <\/pre>\n
<\/p>\n
   6:<\/span> -- Gives you major OS version, Service Pack, Edition, and language info for the operating system<\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- 10.0 is either Windows 10 or Windows Server 2016<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- 6.3 is either Windows 8.1 or Windows Server 2012 R2 <\/span><\/pre>\n
<\/p>\n
   9:<\/span> -- 6.2 is either Windows 8 or Windows Server 2012<\/span><\/pre>\n
<\/p>\n
  10:<\/span> -- 6.1 is either Windows 7 or Windows Server 2008 R2<\/span><\/pre>\n
<\/p>\n
  11:<\/span> -- 6.0 is either Windows Vista or Windows Server 2008<\/span><\/pre>\n
<\/p>\n
  12:<\/span>  <\/pre>\n
<\/p>\n
  13:<\/span> -- Windows SKU codes<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- 4 is Enterprise Edition<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- 7 is Standard Server Edition<\/span><\/pre>\n
<\/p>\n
  16:<\/span> -- 8 is Datacenter Server Edition<\/span><\/pre>\n
<\/p>\n
  17:<\/span> -- 10 is Enterprise Server Edition<\/span><\/pre>\n
<\/p>\n
  18:<\/span> -- 48 is Professional Edition<\/span><\/pre>\n
<\/p>\n
  19:<\/span>  <\/pre>\n
<\/p>\n
  20:<\/span> -- 1033 for os_language_version is US-English<\/span><\/pre>\n
<\/p>\n
  21:<\/span>  <\/pre>\n
<\/p>\n
  22:<\/span> -- SQL Server 2014 requires Windows Server 2012 or newer<\/span><\/pre>\n
<\/p>\n
  23:<\/span>  <\/pre>\n
<\/p>\n
  24:<\/span> -- Quick-Start Installation of SQL Server 2016<\/span><\/pre>\n
<\/p>\n
  25:<\/span> -- https:\/\/msdn.microsoft.com\/en-us\/library\/bb500433(v=sql.130).aspx<\/span><\/pre>\n
<\/p>\n
  26:<\/span>  <\/pre>\n
<\/p>\n
  27:<\/span> -- Hardware and Software Requirements for Installing SQL Server 2016<\/span><\/pre>\n
<\/p>\n
  28:<\/span> -- https:\/\/msdn.microsoft.com\/en-us\/library\/ms143506(v=sql.130).aspx<\/span><\/pre>\n
<\/p>\n
  29:<\/span>  <\/pre>\n
<\/p>\n
  30:<\/span> -- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments<\/span><\/pre>\n
<\/p>\n
  31:<\/span> -- http:\/\/support.microsoft.com\/kb\/2681562<\/a><\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #10 Windows Info<\/strong><\/p>\n
Unfortunately, the raw information that this query returns is a little cryptic. For example, the windows_release column returns a numeric value for the major release number of the operating system, such as 6.3. Unless you have a very good knowledge for computer trivia, you might not know that 6.2 means either Windows 8.1 or Windows Server 2012 R2. That is why I list the relevant possibilities in the comments below the query.<\/p>\n
You also cannot tell directly from the version number alone whether you are dealing with the server operating system (like Windows Server 2012 R2) or a client operating system (like Windows 8.1), even though you should not be running a Production instance of SQL Server on a client operating system. The windows_sku column can sometimes help you indirectly determine that.<\/p>\n
Knowing the details of the operating edition version and edition can be very useful. For example, Windows Server 2008 R2 Standard Edition is limited to 32GB of RAM, which often catches people by surprise.<\/p>\n","protected":false},"excerpt":{"rendered":"
For Day 5 of the series, we have Query #9, which is SQL Server Agent Alerts. This query looks at the sysalerts table on the msdb system database. Query #9 is shown in Figure 1. 1: — Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts) 2: SELECT name, event_source, message_id, severity, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[270],"class_list":["post-1028","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-sql-server-diagnostic-information-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 5 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 5 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"For Day 5 of the series, we have Query #9, which is SQL Server Agent Alerts. This query looks at the sysalerts table on the msdb system database. Query #9 is shown in Figure 1. 1: -- Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts) 2: SELECT name, event_source, message_id, severity, […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-05T22:50:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-11-13T18:52:22+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 5 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-05T22:50:44+00:00\",\"dateModified\":\"2018-11-13T18:52:22+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 5\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 5 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 5 - Glenn Berry","og_description":"For Day 5 of the series, we have Query #9, which is SQL Server Agent Alerts. This query looks at the sysalerts table on the msdb system database. Query #9 is shown in Figure 1. 1: -- Get SQL Server Agent Alert Information (Query 9) (SQL Server Agent Alerts) 2: SELECT name, event_source, message_id, severity, […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-05T22:50:44+00:00","article_modified_time":"2018-11-13T18:52:22+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 5 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-05T22:50:44+00:00","dateModified":"2018-11-13T18:52:22+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-5\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 5"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1028","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1028"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1028\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1028"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1028"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1028"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}