SQL Server Diagnostic Information Queries Detailed, Day 4

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 (RECOMPILE);


   6: -- Tells you the account being used for the SQL Server Service and the SQL Agent Service

   7: -- Shows the process_id, when they were last started, and their current status

   8: -- Shows whether you are running on a failover cluster instance

Figure 1: Query #7 SQL Server Services Info

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).


Query #8 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.

   1: -- Get SQL Server Agent jobs and Category information (Query 8) (SQL Server Agent Jobs)

   2: SELECT sj.name AS [JobName], sj.[description] AS [JobDescription], SUSER_SNAME(sj.owner_sid) AS [JobOwner],

   3: sj.date_created, sj.[enabled], sj.notify_email_operator_id, sj.notify_level_email, sc.name AS [CategoryName],

   4: js.next_run_date, js.next_run_time

   5: FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)

   6: INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)

   7: ON sj.category_id = sc.category_id

   8: LEFT OUTER JOIN msdb.dbo.sysjobschedules AS js WITH (NOLOCK)

   9: ON sj.job_id = js.job_id



  12: -- Gives you some basic information about your SQL Server Agent jobs, who owns them and how they are configured

  13: -- Look for Agent jobs that are not owned by sa

  14: -- Look for jobs that have a notify_email_operator_id set to 0 (meaning no operator)

  15: -- Look for jobs that have a notify_level_email set to 0 (meaning no e-mail is ever sent)

  16: --

  17: -- MSDN sysjobs documentation

  18: -- http://msdn.microsoft.com/en-us/library/ms189817.aspx

Figure 2: Query #8 SQL Server Agent Jobs

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.

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’t 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). 

I think most instances will be in much better shape if they use Ola Hallengren’s SQL Server Maintenance Solution for their backups and routine SQL Server maintenance.

One thought on “SQL Server Diagnostic Information Queries Detailed, Day 4

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.