{"id":4321,"date":"2012-09-11T08:42:09","date_gmt":"2012-09-11T08:42:09","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-monitors-data-repository-alerts\/"},"modified":"2016-07-28T10:51:45","modified_gmt":"2016-07-28T10:51:45","slug":"sql-monitors-data-repository-alerts","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-monitors-data-repository-alerts\/","title":{"rendered":"SQL Monitor’s data repository: Alerts"},"content":{"rendered":"

In my previous post<\/a>, I introduced the SQL Monitor data repository, and described how the monitored objects are stored in a hierarchy in the data<\/em> schema, in a series of tables with a _Keys<\/em> suffix. In this post I had planned to describe how the actual data for the monitored objects is stored in corresponding tables with _StableSamples<\/em> and _UnstableSamples<\/em> suffixes. However, I’m going to postpone that until my next post, as I’ve had a request from a SQL Monitor user to explain how alerts are stored.<\/p>\n

In the SQL Monitor data repository, alerts are stored in tables belonging to the alert<\/em> schema, which contains the following five tables:<\/p>\n

    \n
  1. alert.Alert<\/li>\n
  2. alert.Alert_Cleared<\/li>\n
  3. alert.Alert_Comment<\/li>\n
  4. alert.Alert_Severity<\/li>\n
  5. alert.Alert_Type<\/li>\n<\/ol>\n

    In this post, I’m only going to cover the alert.Alert<\/em> and alert.Alert_Type<\/em> tables. I may cover the other three tables in a later post. The most important table in this schema is alert.Alert<\/em>, as each row in this table corresponds to a single alert. So let’s have a look at it.<\/p>\n

    SELECT TOP 100 AlertId,\n    AlertType,\n    TargetObject,\n    [Read],\n    SubType\n  FROM alert.Alert\n  ORDER BY AlertId DESC;<\/pre>\n
    \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
     <\/th>\nAlertId<\/th>\nAlertType<\/th>\nTargetObject<\/th>\nRead<\/th>\nSubType<\/th>\n<\/tr>\n
    1<\/th>\n65550<\/td>\n39<\/td>\n7:Cluster,1,4:Name,s29:srp-mr03.testnet.red-gate.com,9:SqlServer,1,4:Name,s0:,<\/td>\n1<\/td>\n0<\/td>\n<\/tr>\n
    2<\/th>\n65549<\/td>\n38<\/td>\n7:Cluster,1,4:Name,s29:srp-mr03.testnet.red-gate.com,7:Machine,1,4:Name,s0:,<\/td>\n1<\/td>\n0<\/td>\n<\/tr>\n
    3<\/th>\n65548<\/td>\n18<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s15:FavouriteThings,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    4<\/th>\n65547<\/td>\n15<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s15:FavouriteThings,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    5<\/th>\n65546<\/td>\n14<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s15:FavouriteThings,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    6<\/th>\n65545<\/td>\n18<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s14:SqlMonitorData,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    7<\/th>\n65544<\/td>\n15<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s14:SqlMonitorData,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    8<\/th>\n65543<\/td>\n14<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s14:SqlMonitorData,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    9<\/th>\n65542<\/td>\n18<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s4:msdb,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    10<\/th>\n65541<\/td>\n14<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s4:msdb,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    11<\/th>\n…<\/td>\n <\/td>\n <\/td>\n <\/td>\n <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    So what are we seeing here, then? Well, AlertId<\/em> is an auto-incrementing identity column, so ORDER BY AlertId DESC<\/em> ensures that we see the most recent alerts first. AlertType<\/em> indicates the type of each alert, such as Job failed (6)<\/em>, Backup overdue (14)<\/em> or Long-running query (12)<\/em>. The TargetObject<\/em> column indicates which monitored object the alert is associated with. The Read<\/em> column acts as a flag to indicate whether or not the alert has been read. And finally the SubType<\/em> column is used in the case of a Custom metric (40)<\/em> alert, to indicate which custom metric the alert pertains to.<\/p>\n
    Okay, now lets look at some of those columns in more detail. The AlertType<\/em> column is an easy one to start with, and it brings use nicely to the next table, data.Alert_Type<\/em>. Let’s have a look at what’s in this table:<\/p>\n
    SELECT AlertType,\n    Event,\n    Monitoring,\n    Name,\n    Description\n  FROM alert.Alert_Type\n  ORDER BY AlertType;<\/pre>\n
    \n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
     <\/th>\nAlertType<\/th>\nEvent<\/th>\nMonitoring<\/th>\nName<\/th>\nDescription<\/th>\n<\/tr>\n
    1<\/th>\n1<\/td>\n0<\/td>\n0<\/td>\nProcessor utilization<\/td>\nProcessor utilization (CPU) on a host machine stays above a threshold percentage for longer than a specified duration<\/td>\n<\/tr>\n
    2<\/th>\n2<\/td>\n1<\/td>\n0<\/td>\nSQL Server error log entry<\/td>\nAn error is written to the SQL Server error log with a severity level above a specified value.<\/td>\n<\/tr>\n
    3<\/th>\n3<\/td>\n1<\/td>\n0<\/td>\nCluster failover<\/td>\nThe active cluster node fails, causing the SQL Server instance to switch nodes.<\/td>\n<\/tr>\n
    4<\/th>\n4<\/td>\n1<\/td>\n0<\/td>\nDeadlock<\/td>\nSQL deadlock occurs.<\/td>\n<\/tr>\n
    5<\/th>\n5<\/td>\n0<\/td>\n0<\/td>\nProcessor under-utilization<\/td>\nProcessor utilization (CPU) on a host machine remains below a threshold percentage for longer than a specified duration<\/td>\n<\/tr>\n
    6<\/th>\n6<\/td>\n1<\/td>\n0<\/td>\nJob failed<\/td>\nA job does not complete successfully (the job returns an error code).<\/td>\n<\/tr>\n
    7<\/th>\n7<\/td>\n0<\/td>\n0<\/td>\nMachine unreachable<\/td>\nHost machine (Windows server) cannot be contacted on the network.<\/td>\n<\/tr>\n
    8<\/th>\n8<\/td>\n0<\/td>\n0<\/td>\nSQL Server instance unreachable<\/td>\nThe SQL Server instance is not running or cannot be contacted on the network.<\/td>\n<\/tr>\n
    9<\/th>\n9<\/td>\n0<\/td>\n0<\/td>\nDisk space<\/td>\nDisk space used on a logical disk drive is above a defined threshold for longer than a specified duration.<\/td>\n<\/tr>\n
    10<\/th>\n10<\/td>\n0<\/td>\n0<\/td>\nPhysical memory<\/td>\nPhysical memory (RAM) used on the host machine stays above a threshold percentage for longer than a specified duration.<\/td>\n<\/tr>\n
    11<\/th>\n11<\/td>\n0<\/td>\n0<\/td>\nBlocked process<\/td>\nSQL process is blocked for longer than a specified duration.<\/td>\n<\/tr>\n
    12<\/th>\n12<\/td>\n0<\/td>\n0<\/td>\nLong-running query<\/td>\nA SQL query runs for longer than a specified duration.<\/td>\n<\/tr>\n
    13<\/th>\n14<\/td>\n0<\/td>\n0<\/td>\nBackup overdue<\/td>\nNo full backup exists, or the last full backup is older than a specified time.<\/td>\n<\/tr>\n
    14<\/th>\n15<\/td>\n0<\/td>\n0<\/td>\nLog backup overdue<\/td>\nNo log backup exists, or the last log backup is older than a specified time.<\/td>\n<\/tr>\n
    15<\/th>\n16<\/td>\n0<\/td>\n0<\/td>\nDatabase unavailable<\/td>\nDatabase changes from Online to any other state.<\/td>\n<\/tr>\n
    16<\/th>\n17<\/td>\n0<\/td>\n0<\/td>\nPage verification<\/td>\nTorn Page Detection or Page Checksum is not enabled for a database.<\/td>\n<\/tr>\n
    17<\/th>\n18<\/td>\n0<\/td>\n0<\/td>\nIntegrity check overdue<\/td>\nNo entry for an integrity check (DBCC DBINFO returns no date for dbi_dbccLastKnownGood field), or the last check is older than a specified time.<\/td>\n<\/tr>\n
    18<\/th>\n19<\/td>\n0<\/td>\n0<\/td>\nFragmented indexes<\/td>\nFragmentation level of one or more indexes is above a threshold percentage.<\/td>\n<\/tr>\n
    19<\/th>\n24<\/td>\n0<\/td>\n0<\/td>\nJob duration unusual<\/td>\nThe duration of a SQL job duration deviates from its baseline duration by more than a threshold percentage.<\/td>\n<\/tr>\n
    20<\/th>\n25<\/td>\n0<\/td>\n1<\/td>\nClock skew<\/td>\nSystem clock time on the Base Monitor computer differs from the system clock time on a monitored SQL Server host machine by a specified number of seconds.<\/td>\n<\/tr>\n
    21<\/th>\n27<\/td>\n0<\/td>\n0<\/td>\nSQL Server Agent Service status<\/td>\nThe SQL Server Agent Service status matches the status specified.<\/td>\n<\/tr>\n
    22<\/th>\n28<\/td>\n0<\/td>\n0<\/td>\nSQL Server Reporting Service status<\/td>\nThe SQL Server Reporting Service status matches the status specified.<\/td>\n<\/tr>\n
    23<\/th>\n29<\/td>\n0<\/td>\n0<\/td>\nSQL Server Full Text Search Service status<\/td>\nThe SQL Server Full Text Search Service status matches the status specified.<\/td>\n<\/tr>\n
    24<\/th>\n30<\/td>\n0<\/td>\n0<\/td>\nSQL Server Analysis Service status<\/td>\nThe SQL Server Analysis Service status matches the status specified.<\/td>\n<\/tr>\n
    25<\/th>\n31<\/td>\n0<\/td>\n0<\/td>\nSQL Server Integration Service status<\/td>\nThe SQL Server Integration Service status matches the status specified.<\/td>\n<\/tr>\n
    26<\/th>\n33<\/td>\n0<\/td>\n0<\/td>\nSQL Server Browser Service status<\/td>\nThe SQL Server Browser Service status matches the status specified.<\/td>\n<\/tr>\n
    27<\/th>\n34<\/td>\n0<\/td>\n0<\/td>\nSQL Server VSS Writer Service status<\/td>\nThe SQL Server VSS Writer status matches the status specified.<\/td>\n<\/tr>\n
    28<\/th>\n35<\/td>\n0<\/td>\n1<\/td>\nDeadlock trace flag disabled<\/td>\nThe monitored SQL Server’s trace flag cannot be enabled.<\/td>\n<\/tr>\n
    29<\/th>\n36<\/td>\n0<\/td>\n0<\/td>\nMonitoring stopped (host machine credentials)<\/td>\nSQL Monitor cannot contact the host machine because authentication failed.<\/td>\n<\/tr>\n
    30<\/th>\n37<\/td>\n0<\/td>\n0<\/td>\nMonitoring stopped (SQL Server credentials)<\/td>\nSQL Monitor cannot contact the SQL Server instance because authentication failed.<\/td>\n<\/tr>\n
    31<\/th>\n38<\/td>\n0<\/td>\n0<\/td>\nMonitoring error (host machine data collection)<\/td>\nSQL Monitor cannot collect data from the host machine.<\/td>\n<\/tr>\n
    32<\/th>\n39<\/td>\n0<\/td>\n0<\/td>\nMonitoring error (SQL Server data collection)<\/td>\nSQL Monitor cannot collect data from the SQL Server instance.<\/td>\n<\/tr>\n
    33<\/th>\n40<\/td>\n0<\/td>\n0<\/td>\nCustom metric<\/td>\nThe custom metric value has passed an alert threshold.<\/td>\n<\/tr>\n
    34<\/th>\n41<\/td>\n0<\/td>\n0<\/td>\nCustom metric collection error<\/td>\nSQL Monitor cannot collect custom metric data from the target object.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    Basically, alert.Alert_Type<\/em> is just a big reference table containing information about the 34 different alert types supported by SQL Monitor (note that the largest id is 41, not 34 – some alert types have been retired since SQL Monitor was first developed). The Name<\/em> and Description<\/em> columns are self evident, and I’m going to skip over the Event<\/em> and Monitoring<\/em> columns as they’re not very interesting. The AlertId<\/em> column is the primary key, and is referenced by AlertId<\/em> in the alert.Alert<\/em> table. As such, we can rewrite our earlier query to join these two tables, in order to provide a more readable view of the alerts:<\/p>\n
    SELECT TOP 100 AlertId,\n    Name,\n    TargetObject,\n    [Read],\n    SubType\n  FROM alert.Alert a \n    JOIN alert.Alert_Type at ON a.AlertType = at.AlertType\n  ORDER BY AlertId DESC;<\/pre>\n
    \n\n\n\n\n\n\n\n\n\n\n\n\n\n
     <\/th>\nAlertId<\/th>\nName<\/th>\nTargetObject<\/th>\nRead<\/th>\nSubType<\/th>\n<\/tr>\n
    1<\/th>\n65550<\/td>\nMonitoring error (SQL Server data collection)<\/td>\n7:Cluster,1,4:Name,s29:srp-mr03.testnet.red-gate.com,9:SqlServer,1,4:Name,s0:,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    2<\/th>\n65549<\/td>\nMonitoring error (host machine data collection)<\/td>\n7:Cluster,1,4:Name,s29:srp-mr03.testnet.red-gate.com,7:Machine,1,4:Name,s0:,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    3<\/th>\n65548<\/td>\nIntegrity check overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s15:FavouriteThings,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    4<\/th>\n65547<\/td>\nLog backup overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s15:FavouriteThings,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    5<\/th>\n65546<\/td>\nBackup overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s15:FavouriteThings,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    6<\/th>\n65545<\/td>\nIntegrity check overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s14:SqlMonitorData,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    7<\/th>\n65544<\/td>\nLog backup overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s14:SqlMonitorData,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    8<\/th>\n65543<\/td>\nBackup overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s14:SqlMonitorData,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    9<\/th>\n65542<\/td>\nIntegrity check overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s4:msdb,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n
    10<\/th>\n65541<\/td>\nBackup overdue<\/td>\n7:Cluster,1,4:Name,s7:granger,9:SqlServer,1,4:Name,s0:,8:Database,1,4:Name,s4:msdb,<\/td>\n0<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n
    Okay, the next column to discuss in the alert.Alert<\/em> table is TargetObject<\/em>. Oh boy, this one’s a bit tricky! The TargetObject<\/em> of an alert is a serialized string representation of the position in the monitored object hierarchy of the object to which the alert pertains. The serialization format is somewhat convenient for parsing in the C# source code of SQL Monitor, and has some helpful characteristics, but it’s probably very awkward to manipulate in T-SQL.<\/p>\n
    I could document the serialization format here, but it would be very dry reading, so perhaps it’s best to consider an example from the table above. Have a look at the alert with an AlertID<\/em> of 65543. It’s a Backup overdue<\/em> alert for the SqlMonitorData<\/em> database running on the default instance of granger<\/em>, my laptop. Each different alert type is associated with a specific type of monitored object in the object hierarchy (I described the hierarchy in my previous post<\/a>). The Backup overdue<\/em> alert is associated with databases, whose position in the object hierarchy is root → Cluster → SqlServer → Database<\/em>. The TargetObject<\/em> value identifies the target object by specifying the key properties at each level in the hierarchy, thus:<\/p>\n