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)

Help and Information

Getting SQL Server 2005 Assistance