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

Continuing the series, Query #7<\/strong> is SQL Server Services Info. It retrieves this information from the sys.dm_server_services<\/a> DMV. Query 7 is shown in Figure 1.<\/p>\n

\n
\n
   1:<\/span> -- SQL Server Services information (Query 7) (SQL Server Services Info)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> servicename, process_id, startup_type_desc, status_desc, <\/pre>\n
<\/p>\n
   3:<\/span> last_startup_time, service_account, is_clustered, cluster_nodename, [filename]<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_server_services WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   5:<\/span>  <\/pre>\n
<\/p>\n
   6:<\/span> -- Tells you the account being used for the SQL Server Service and the SQL Agent Service<\/span><\/pre>\n
<\/p>\n
   7:<\/span> -- Shows the process_id, when they were last started, and their current status<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- Shows whether you are<\/span> running on<\/span> a failover cluster instance<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #7 SQL Server Services Info<\/strong><\/p>\n
This query tells you the last time that the SQL Server Service started, which is very important information when it comes to interpreting the results of many of the other queries in this set. It also tells you the Service account, and the startup type for the Database Engine and SQL Server Agent, along with their startup types and process ID. It also tells you whether you have a SQL Server failover cluster instance (FCI).<\/p>\n
 <\/p>\n
Query #8<\/strong> is SQL Server Agent Jobs. This query returns information about SQL Server Agent jobs from the sysjobs and syscategories tables in the msdb system database. This query gives you a good high-level view of all of the SQL Server Agent jobs on your instance. Query #8 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get SQL Server Agent jobs and Category information (Query 8) (SQL Server Agent Jobs)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> sj.name AS<\/span> [JobName], sj.[description] AS<\/span> [JobDescription], SUSER_SNAME(sj.owner_sid) AS<\/span> [JobOwner],<\/pre>\n
<\/p>\n
   3:<\/span> sj.date_created, sj.[enabled], sj.notify_email_operator_id, sj.notify_level_email, sc.name AS<\/span> [CategoryName],<\/pre>\n
<\/p>\n
   4:<\/span> js.next_run_date, js.next_run_time<\/pre>\n
<\/p>\n
   5:<\/span> FROM<\/span> msdb.dbo.sysjobs AS<\/span> sj WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   6:<\/span> INNER<\/span> JOIN<\/span> msdb.dbo.syscategories AS<\/span> sc WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   7:<\/span> ON<\/span> sj.category_id = sc.category_id<\/pre>\n
<\/p>\n
   8:<\/span> LEFT<\/span> OUTER<\/span> JOIN<\/span> msdb.dbo.sysjobschedules AS<\/span> js WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   9:<\/span> ON<\/span> sj.job_id = js.job_id<\/pre>\n
<\/p>\n
  10:<\/span> ORDER<\/span> BY<\/span> sj.name OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
  11:<\/span>  <\/pre>\n
<\/p>\n
  12:<\/span> -- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured<\/span><\/pre>\n
<\/p>\n
  13:<\/span> -- Look for Agent jobs that are not owned by sa<\/span><\/pre>\n
<\/p>\n
  14:<\/span> -- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)<\/span><\/pre>\n
<\/p>\n
  15:<\/span> -- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)<\/span><\/pre>\n
<\/p>\n
  16:<\/span> --<\/span><\/pre>\n
<\/p>\n
  17:<\/span> -- MSDN sysjobs documentation<\/span><\/pre>\n
<\/p>\n
  18:<\/span> -- http:\/\/msdn.microsoft.com\/en-us\/library\/ms189817.aspx<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #8 SQL Server Agent Jobs<\/strong><\/p>\n
I like to focus on the JobOwner column, which should be sa rather than an individual login, whether the job is enabled or not, and whether there is a notify_email_operator specified. <\/p>\n
I also like to see whether people are using jobs from the evil SQL Server Maintenance Plan Wizard that is built into SSMS. The SSMS Maintenance Plan Wizard (prior to SQL Server 2016) makes it far too easy for people to do dumb things, because they don\u2019t know any better. For example, you can have SQL Server rebuild all the indexes in a database (whether they need it or not) and then update statistics (which is not necessary when you rebuild an index in SQL Server).  <\/p>\n
I think most instances will be in much better shape if they use Ola Hallengren\u2019s SQL Server Maintenance Solution<\/a> for their backups and routine SQL Server maintenance.<\/p>\n","protected":false},"excerpt":{"rendered":"
Continuing the series, Query #7 is SQL Server Services Info. It retrieves this information from the sys.dm_server_services DMV. Query 7 is shown in Figure 1. 1: — SQL Server Services information (Query 7) (SQL Server Services Info) 2: SELECT servicename, process_id, startup_type_desc, status_desc, 3: last_startup_time, service_account, is_clustered, cluster_nodename, [filename] 4: FROM sys.dm_server_services WITH (NOLOCK) OPTION […]<\/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-1027","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 4 - 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-4\/\" \/>\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 4 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"Continuing the series, Query #7 is SQL Server Services Info. It retrieves this information from the sys.dm_server_services DMV. Query 7 is shown in Figure 1. 1: -- SQL Server Services information (Query 7) (SQL Server Services Info) 2: SELECT servicename, process_id, startup_type_desc, status_desc, 3: last_startup_time, service_account, is_clustered, cluster_nodename, [filename] 4: FROM sys.dm_server_services WITH (NOLOCK) OPTION […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-04T20:03:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T19:28:43+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-4\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 4 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-04T20:03:14+00:00\",\"dateModified\":\"2017-04-13T19:28:43+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-4\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/#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 4\"}]},{\"@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 4 - 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-4\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 4 - Glenn Berry","og_description":"Continuing the series, Query #7 is SQL Server Services Info. It retrieves this information from the sys.dm_server_services DMV. Query 7 is shown in Figure 1. 1: -- SQL Server Services information (Query 7) (SQL Server Services Info) 2: SELECT servicename, process_id, startup_type_desc, status_desc, 3: last_startup_time, service_account, is_clustered, cluster_nodename, [filename] 4: FROM sys.dm_server_services WITH (NOLOCK) OPTION […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-04T20:03:14+00:00","article_modified_time":"2017-04-13T19:28:43+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-4\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 4 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-04T20:03:14+00:00","dateModified":"2017-04-13T19:28:43+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-4\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-4\/#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 4"}]},{"@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\/1027","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=1027"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1027\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1027"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1027"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1027"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}