{"id":1030,"date":"2016-01-07T08:07:29","date_gmt":"2016-01-07T16:07:29","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1030"},"modified":"2017-04-13T12:28:44","modified_gmt":"2017-04-13T19:28:44","slug":"sql-server-diagnostic-information-queries-detailed-day-7","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 7"},"content":{"rendered":"

Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group.<\/p>\n

For Day 7 of this series, we start out with Query #13<\/strong>, which is SQL Server Error Log. This query retrieves information from the sys.dm_os_server_diagnostics_log_configurations<\/a> dynamic management view with the current configuration for the SQL Server failover cluster diagnostic log. Query #13 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- Shows you where the SQL Server failover cluster diagnostic log is located and how it is configured  (Query 13) (SQL Server Error Log)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> is_enabled, [path<\/span>], max_size, max_files<\/pre>\n
<\/p>\n
   3:<\/span> FROM<\/span> sys.dm_os_server_diagnostics_log_configurations WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   4:<\/span>  <\/pre>\n
<\/p>\n
   5:<\/span> -- Knowing this information is important for troubleshooting purposes<\/span><\/pre>\n
<\/p>\n
   6:<\/span> -- Also shows you the location of<\/span> other error and<\/span> diagnostic log files<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #13 SQL Server Error Log<\/strong><\/p>\n
This query basically shows you where all the SQL Server related error and diagnostic logs are located. Knowing this can be very helpful if you need to troubleshoot problems with various parts of SQL Server. For example, if the SQL Server database engine will not start, opening the SQL Server Error log in a text editor will often give you some very useful information about what the problem is.<\/p>\n
 <\/p>\n
Query #14<\/strong> is Cluster Node Properties. This query retrieves information from the sys.dm_os_cluster_nodes<\/a> dynamic management view with one row for each node in the failover cluster instance configuration. If you have a standalone instance of SQL Server, this query will return an empty rowset. Query #14 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get information about your cluster nodes and their status  (Query 14) (Cluster Node Properties)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> -- (if your database server is in a failover cluster)<\/span><\/pre>\n
<\/p>\n
   3:<\/span> SELECT<\/span> NodeName, status_description, is_current_owner<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_os_cluster_nodes WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   5:<\/span>  <\/pre>\n
<\/p>\n
   6:<\/span> -- Knowing which node owns the cluster resources is critical<\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- Especially when you are installing Windows or SQL Server updates<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- You will see no<\/span> results if<\/span> your instance is<\/span> not<\/span> clustered<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #14 Cluster Node Properties<\/strong><\/p>\n
This query is useful because it lets you identify each node in the cluster, see it\u2019s current status, and see which node currently owns the instance.<\/p>\n
 <\/p>\n
Query #15<\/strong> is AlwaysOn AG Cluster. This query retrieves information from the sys.dm_hadr_cluster<\/a> dynamic management view about the Windows Server Failover Clustering (WSFC) quorum, if one exists on the node that is hosting the SQL Server instance, and you are using an AlwaysOn AG. Query #15 is shown in Figure 3.<\/p>\n
\n
\n
   1:<\/span> -- Get information about any AlwaysOn AG cluster this instance is a part of (Query 15) (AlwaysOn AG Cluster)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> cluster_name, quorum_type_desc, quorum_state_desc<\/pre>\n
<\/p>\n
   3:<\/span> FROM<\/span> sys.dm_hadr_cluster WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   4:<\/span>  <\/pre>\n
<\/p>\n
   5:<\/span> -- You will see no results if your instance is not using AlwaysOn AGs<\/span><\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span> -- Recommended hotfixes and updates for Windows Server 2012 R2-based failover clusters<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- http:\/\/support.microsoft.com\/kb\/2920151<\/a><\/pre>\n
<\/div>\n<\/div>\n
Figure 3: Query #15 AlwaysOn AG Cluster<\/strong><\/p>\n
This query is one way of detecting the fact that there is an AlwaysOn AG in place on your instance, and then seeing what quorum type is being used.<\/p>\n
If you are going to be using either traditional FCIs or AlwaysOn AGs (which require WSFC, but not shared storage), it is very important that you use Windows Server 2012 or newer for your operating system. The clustering code was basically rewritten in Windows Server 2012, and it was further improved in Windows Server 2012 R2.<\/p>\n","protected":false},"excerpt":{"rendered":"
Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group. For Day 7 of this series, we start out with Query #13, which is SQL Server Error Log. This query retrieves information from the […]<\/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-1030","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 7 - 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-7\/\" \/>\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 7 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group. For Day 7 of this series, we start out with Query #13, which is SQL Server Error Log. This query retrieves information from the […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-07T16:07:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:44+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=\"3 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-7\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 7 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-07T16:07:29+00:00\",\"dateModified\":\"2017-04-13T19:28:44+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-7\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/#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 7\"}]},{\"@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 7 - 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-7\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 7 - Glenn Berry","og_description":"Note: The three queries I am discussing today are not particularly interesting or useful if you are not using either a traditional, failover cluster instance (FCI), or an AlwaysOn Availability Group. For Day 7 of this series, we start out with Query #13, which is SQL Server Error Log. This query retrieves information from the […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-07T16:07:29+00:00","article_modified_time":"2017-04-13T19:28:44+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 7 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-07T16:07:29+00:00","dateModified":"2017-04-13T19:28:44+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-7\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-7\/#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 7"}]},{"@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\/1030","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=1030"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1030\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1030"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1030"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1030"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}