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