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)
5: ORDER BY name OPTION (RECOMPILE);
6:
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);
5:
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
12:
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
19:
20: -- 1033 for os_language_version is US-English
21:
22: -- SQL Server 2014 requires Windows Server 2012 or newer
23:
24: -- Quick-Start Installation of SQL Server 2016
25: -- https://msdn.microsoft.com/en-us/library/bb500433(v=sql.130).aspx
26:
27: -- Hardware and Software Requirements for Installing SQL Server 2016
28: -- https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx
29:
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.