NSSetQuantumClockDate (Transact-SQL)

Resets the Microsoft SQL Server Notification Services application quantum clock to a specific time, which is useful for continually running an application over the same time interval for testing purposes by setting an exact time, such as 9:00 Monday morning.

The following two scenarios show how you can use this stored procedure:

  • When testing an application, you can add one scheduled subscription, and then test that subscription by resetting the quantum clock as needed.
  • When diagnosing system problems, you can reset the quantum clock and reproduce past system behavior. All processing, such as event batches and timed subscriptions, is replayed exactly as in real time, as long as the instance is running and enabled.

    [ @QuantumStartTime = ] 'start_date_time'

[ @QuantumStartTime = ] 'start_date_time'

Is a previous starting datetime value, in UTC (Coordinated Universal Time), for the quantum clock. This forces the generator to start a new quantum at a past time, replaying previous processing. Because the time is in the past, the generator processes the quantum as soon as possible.

0 (success) or 1 (failure)

Notification Services creates the NSSetQuantumClockDate stored procedure in the application database when you create the instance. When you update the application, Notification Services recompiles the stored procedure.

This stored procedure is in the application's schema, which is specified by the SchemaName element of the application definition file (ADF). If no schema name is provided, the default schema is dbo.

You must disable the generator before running NSSetQuantumClockDate.

NSSetQuantumClockDate initializes the quantum clock, so the @QuantumStartTime value is included within the first quantum processed by the generator after running the procedure.

To find a quantum start time, use the NSQuantumList stored procedure. The result set shows start-time and end-time values, in UTC, for each quantum.

In the application definition file (ADF), if the ChronicleQuantumLimit and SubscriptionQuantumLimit values are not zero (which means there is no limit to how far back you can process quanta), the generator might skip the quantum of interest.

Vacuum processing removes data from the system. If you have removed data of interest, the processing of that data cannot be replayed.

When Notification Services tries to catch up on prior quanta, the older quanta take precedence and are processed first. When you initialize the quantum clock, be aware that new quanta might be processed later than scheduled.

Execute permissions default to members of the NSGenerator and NSRunService database roles, db_owner fixed database role, and sysadmin fixed server role.

The following example shows how to reset the quantum clock to replay what occurred on April 23, 2004, at 9:00 A.M. Pacific daylight time. The equivalent UTC time is 17:00, and the EndTime column in the NSQuantum1 table shows that the previous quantum ended at 16:59.

The application uses the default SchemaName settings, which places all application objects in the dbo schema.

EXEC dbo.NSSetQuantumClockDate 
    @QuantumStartTime = '2004-04-23 16:59';

Community Additions