SQL Sentry Event Objects
Introduction
At a high level, an event object is any job, task, or other event type that can be managed using SQL Sentry. There are two general types of events: active event objects and status events. The two are actually completely different, with the exception that their event instances occur at some measurable point in time, and therefore can be represented chronologically in Calendar and List views. A third sub-type is a hybrid event object that contains some characteristics of both active and/or status events.
Types of Event Objects
When you view a calendar in SQL Sentry, you'll see a combination of various types of active and status event instances. Active event objects include SQL Agent jobs, maintenance plans, and reporting services reports, along with Windows task scheduler tasks. Status events include SQL Agent alerts and SQL Agent log records. Busy calendars can include some or all of these event types, so it's important to be able to differentiate them to make the most of the information presented. The SQL Sentry client indicates the type of event object using a small glyph on each event instance, as well as in event instance pop-up windows.
| Event Objects | Type | Instance Type |
|---|---|---|
| SQL Server Agent Job | Active |
|
| SQL Server Agent Alert | Status (hybrid) | SQL Server |
| SQL Server Agent Log Record | Status | SQL Server |
| Maintenance Plan | Active (hybrid) | SQL Server |
| Reporting Services Report | Active | SQL Server |
| Windows Task | Active | Windows |
| Windows Event Logs Event | Status | Windows |
Active Event Objects
An active event object is commonly referred to as a job or a task, and contains all of the information necessary to perform some measurable work. This can include instructions in some scripting language (T-SQL, VBScript, etc.), connection information, configuration parameters, etc. Active events always have a start time and an end time, (they have duration), and they are schedulable. SQL Agent jobs and Windows tasks are examples of active event objects.
Status Events
A status event represents the discrete occurrence of some condition of a process, usually a persistent process. Status events don't have duration, they aren't schedulable, and they don't perform any measurable work. For example, the SQL Server Agent log contains various status events given off by the SQL Server process at specific points in time.
Status Event Objects
A status event object is any status event that is predefined and/or configurable, such as a SQL Server Agent error condition. Most entries in the SQL Agent logs are instances of a status event object.
For example, an attempt was made to send an email when no email session was established.
Although this event object isn't configurable, it represents an error condition that is predefined and static.
Ad Hoc Status Events
Not all status events can be pre-defined; for example, an ad hoc message for a transaction log backup recorded in the SQL Server log:
Log backed up: Database: Northwind, creation date(time): 2004/07/27(11:51:24), first LSN: 238:292:1, last LSN: 238:292:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\
In this case, a discrete status event is logged as the result of some process, but it doesn't specify a start or end time, nor is the output constant, i.e., it changes every time. It's also not entirely pre-defined nor is it configurable; therefore we consider this an ad hoc status event.
Hybrid Event Objects
Hybrid event objects are those that don't necessarily fall cleanly into the active or status event categories.
For example, a SQL Agent maintenance plan is an active event object that is comprised of several other active event objects; SQL Agent jobs. The plan itself doesn't do any work, the jobs do; they handle the various activities associated with the plan such as backups, optimizations, etc. In this case, the jobs act as surrogates for the plan itself. The plan does, however, contain configuration information, as well as an execution engine and log, and its associated jobs do work and have duration. Therefore, we consider the plan a variant of an active event object. If it didn't have its own configuration info or execution log, it would simply be a collection of jobs.
A SQL Agent alert is a variant of a status event object. Alerts can be triggered by various conditions of the SQL Server process, such as a full transaction log, a corrupt database, etc. The SQL Server logs a status event to the Windows application log, then the SQL Agent picks it up and fires any defined alerts that match the condition. Although alerts don't have their own log, when they fire SQL Sentry is able to capture the output as a discrete status event instance and log it to a table in MSDB, effectively an event log. SQL Agent alerts are more of a tool for the DBA to define the events that they want to be notified or take some other action; they aren't really status event objects in the strictest sense. For our purposes, it helps to think of them that way.
Event Instances
An event instance is any individual historical or future occurrence of an active event object or status event. Event instances correlate to the rectangular placeholders displayed on a SQL Sentry calendar, or the master records shown in a list view. An individual execution of a SQL Agent job represents an active event instance. Every occurrence of a SQL Agent error in the SQL Agent logs is an individual status event instance.
Event Object Information Window
When you right-click any event object in the Navigator pane, and then select Open or double-click an event instance on the Calendar view, Navigator pane, or Runtime Graph, a tab for the event object's Information Window appears. The window contains various metadata about the object, and the information displayed changes based upon the type of object.
The active event objects, runtime statistics, and the Calculate Runtime Statistics Since option are shown, as well as schedules for objects that may be scheduled such as jobs, tasks and reports. Tasks include additional data like the option to define a success exit code.
Recalculating Runtime Statistics
On the information window for all active event objects, you'll see the Calculate Runtime Statistics Since option. Use this feature whenever a major change is made to an event definition that may cause its typical runtime to increase or decrease dramatically. An example might be removing a long-running step from a job. Re-calculating runtime statistics in this case can help avoid erroneous notifications for changes in minimum or maximum runtime. Check the box next to Calculate Runtime Statistics Since, and enter the date and time the event was changed, and then select Save on the toolbar. This causes SQL Sentry to immediately recalculate runtime statistics using only run history since the specified date/time. This value is also used whenever the SQL Sentry monitoring service auto-calculates statistics for the event in the future.