NSAdministrationHistory (Transact-SQL)
Produces the administrative history report for an instance of Microsoft SQL Server Notification Services. The report contains information about all applications hosted by the instance, including the events, notifications, and subscriptions processed during a defined time interval.
Syntax
[ schema_name . ] NSAdministrationHistory
[ [ @ApplicationName = ] 'app_name' ],
[, [ @ReportingIntervalInMinutes = ] interval ]
[, [ @StartDateTime = ] 'start_date_time' ]
[, [ @EndDateTime = ] 'end_date_time' ]
Arguments
- [ @ApplicationName = ] 'app_name'
Is the name of the Notification Services application, as defined in the instance configuration file (ICF). app_name is nvarchar(255) and has a default value of NULL, which indicates to report on all applications hosted by the instance.
- [ @ReportingIntervalInMinutes = ] interval
Is the number of minutes in each reporting interval. The report contains one row per interval. interval is int and has a default value of 60, which indicates 60 minutes per reporting interval.
- [ @StartDateTime = ] 'start_date_time'
Is the starting date and time for the report in UTC (Coordinated Universal Time or Greenwich Mean Time). start_date_time is datetime and has a default value of one month prior to the @EndDateTime value.
- [ @EndDateTime = ] 'end_date_time'
Is the report end date and time in UTC. The end_date_time value has a data type of datetime. The default value is the result of the GETUTCDATE function, which returns the time at which the stored procedure was invoked.
Return Code Values
None
Result Sets
| Column Name | Data Type | Description |
|---|---|---|
ApplicationName |
nvarchar(255) |
Name of the application that the row summarizes. This value is NULL for the row summarizing the instance. |
IntervalStartDateTime |
datetime |
Starting date and time for the reporting interval in UTC. |
IntervalEndDateTime |
datetime |
Ending date and time for the reporting interval in UTC. |
EventBatchCollectedCount |
int |
Number of event batches collected during the reporting interval, based on the event batch EndCollectionTime value. |
EventsCollectedCount |
int |
Number of events collected during the reporting interval, based on the event batch EndCollectionTime value. |
EventsCollectedPerSecond |
float |
During event collection, average number of events collected per second during the reporting interval. |
EventBatchesAwaitingGeneration |
int |
Number of event batches written to the database but not processed by the generator. |
EventBatchesInCollection |
int |
Number of event batches in the event collection stage during the reporting interval. |
NotificationBatchGeneratedCount |
int |
Number of notification batches written during the reporting interval, based on the notification batch EndGenerationTime value. |
NotificationsGeneratedCount |
int |
Number of notifications generated during the reporting interval, based on the notification batch EndGenerationTime value. |
NotificationsGeneratedPerSecond |
float |
Average number of notifications generated per second during the reporting interval. |
NotificationBatchesInGeneration |
int |
Number of notification batches in the process of being created by the generator during the reporting interval. |
NotificationBatchesAwaitingDistribution |
int |
Number of notification batches waiting to be picked up by the distributor during the reporting interval. |
NotificationsSuccessfulDeliveredCount |
int |
Number of notifications successfully delivered during the reporting interval. |
NotificationsFailedDeliveryCount |
int |
Number of notifications that failed delivery and were marked as expired during the reporting interval. |
WorkItemsInProgress |
int |
Number of work items being distributed during the reporting interval. |
SubscriptionsAddedCount |
int |
Number of subscriptions added to the application during the reporting interval. |
ScheduledSubscriptionsAdded |
int |
Number of scheduled subscriptions added to the application during the reporting interval. |
SubscriptionsModified |
int |
Number of subscriptions modified in the application during the reporting interval. |
SubscribersAddedCount |
int |
Number of subscribers added to the instance during the reporting interval. |
SubscriberDevicesAddedCount |
int |
Number of subscriber devices added during the reporting interval. The device is assumed to be added at the same time that the subscriber is added. |
SubscriberDevicesModifiedCount |
int |
Number of subscriber devices updated during the reporting interval. The device is assumed to be updated at the same time that the subscriber is updated. |
Remarks
Notification Services creates the NSAdministrationHistory stored procedure in the instance database when you create the instance. When you update the instance, Notification Services recompiles the stored procedure.
This stored procedure is in the instance schema, which is specified by the SchemaName element of the instance configuration file (ICF). If no schema name is provided, the default schema is dbo.
The amount of data gathered by this report is limited by the vacuum process retention period specified in the VacuumDuration element of the application definition file (ADF). Only data that has not been removed can be analyzed.
To determine the current UTC date and time, run the SELECT GETUTCDATE() Transact-SQL query. The current UTC time is derived from the current local time and the time zone setting in the computer's operating system.
Permissions
Execute permissions default to members of the NSAnalysis database role, the db_owner fixed database role, and the sysadmin fixed server role.
Examples
A. Flight Administrative History
The following example produces the administrative history report for the Flight application. The instance uses the default database settings, which places all instance objects in the dbo schema.
The report interval is 120 minutes. The report starts at 11:30 A.M. on September 22, 2004, and ends at 2:30 P.M. the same day.
EXEC dbo.NSAdministrationHistory
@ApplicationName = N'Flight',
@ReportingIntervalInMinutes = 120,
@StartDateTime = '2004-09-22 11:30:00',
@EndDateTime = '2004-09-22 14:30:00';
B. All Application Administrative History
The following example produces the administrative history report for all applications hosted by the instance. In this example, the stored procedure is in the FlightInstance schema, as specified in the SchemaName element of the ICF.
The report uses default values, which specifies to show all data for the past month using a reporting interval of 60 minutes.
EXEC FlightInstance.NSAdministrationHistory;
See Also
Reference
Notification Services Stored Procedures (Transact-SQL)
Other Resources
Notification Services Performance Reports
SchemaName Element (ICF)