SQL Server Diagnostic Information Queries Detailed, Day 5

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, [enabled], has_notification, 

   3:        delay_between_responses, occurrence_count, last_occurrence_date, last_occurrence_time

   4: FROM msdb.dbo.sysalerts WITH (NOLOCK)



   7: -- Gives you some basic information about your SQL Server Agent Alerts (which are different from SQL Server Agent jobs)

   8: -- Read more about Agent Alerts here: https://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

Figure 1: Query #9 SQL Server Agent Alerts

In my experience, probably 90% of the SQL Server instances that I look at don’t have any SQL Server Agent Alerts in place, often because many DBAs are not sure what they are or why they are useful.

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.

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


Query #10 is Windows Info. This query retrieves information from the sys.dm_os_windows_info dynamic management view about the operating system that you are running your instance of SQL Server on. Query #10 is shown in Figure 2.

   1: -- Windows information (Query 10) (Windows Info)

   2: SELECT windows_release, windows_service_pack_level, 

   3:        windows_sku, os_language_version

   4: FROM sys.dm_os_windows_info WITH (NOLOCK) OPTION (RECOMPILE);


   6: -- Gives you major OS version, Service Pack, Edition, and language info for the operating system

   7: -- 10.0 is either Windows 10 or Windows Server 2016

   8: -- 6.3 is either Windows 8.1 or Windows Server 2012 R2 

   9: -- 6.2 is either Windows 8 or Windows Server 2012

  10: -- 6.1 is either Windows 7 or Windows Server 2008 R2

  11: -- 6.0 is either Windows Vista or Windows Server 2008


  13: -- Windows SKU codes

  14: -- 4 is Enterprise Edition

  15: -- 7 is Standard Server Edition

  16: -- 8 is Datacenter Server Edition

  17: -- 10 is Enterprise Server Edition

  18: -- 48 is Professional Edition


  20: -- 1033 for os_language_version is US-English


  22: -- SQL Server 2014 requires Windows Server 2012 or newer


  24: -- Quick-Start Installation of SQL Server 2016

  25: -- https://msdn.microsoft.com/en-us/library/bb500433(v=sql.130).aspx


  27: -- Hardware and Software Requirements for Installing SQL Server 2016

  28: -- https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx


  30: -- Using SQL Server in Windows 8, Windows 8.1, Windows Server 2012 and Windows Server 2012 R2 environments

  31: -- http://support.microsoft.com/kb/2681562

Figure 2: Query #10 Windows Info

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.

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.

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.

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.