Edit

Query Notifications - sys.dm_qn_subscriptions

  • Article

Applies to: SQL Server

Returns information about the active query notifications subscriptions in the server. You can use this view to check for active subscriptions in the server or a specified database, or to check for a specified server principal.

Column name Data type Description
id int ID of a subscription.
database_id int ID of the database in which the notification query was executed. This database stores information related to this subscription.
sid varbinary(85) Security ID of the server principal that created and owns this subscription.
object_id int ID of the internal table that stores information about subscription parameters.
created datetime Date and time that the subscription was created.
timeout int Time-out for the subscription in seconds. The notification will be flagged to fire after this time has elapsed.

Note: The actual firing time may be greater than the specified time-out. However, if a change that invalidates the subscription occurs after the specified time-out, but before the subscription is fired, SQL Server ensures that firing occurs at the time that the change was made.
status int Indicates the status of the subscription. See the table under remarks for the list of codes.

Relationship Cardinalities

From To On Type
sys.dm_qn_subscriptions sys.databases database_id Many-to-one
sys.dm_qn_subscriptions sys.internal_tables object_id Many-to-one

Remarks

The status code of 0 indicates an undefined status.

The following status codes indicate that a subscription fired because of a change:

Code Minor status Info
65798 Subscription fired because data changed Subscription triggered by insert
65799 Subscription fired because data changed Delete
65800 Subscription fired because data changed Update
65801 Subscription fired because data changed Merge
65802 Subscription fired because data changed Truncate table
66048 Subscription fired because timeout expired Undefined info mode
66315 Subscription fired because object changed object or user was dropped
66316 Subscription fired because object changed object was altered
66565 Subscription fired because database was detached or dropped server or db restarted
66571 Subscription fired because database was detached or dropped object or user was dropped
66572 Subscription fired because database was detached or dropped object was altered
67341 subscription was triggered because of lack od resources on the server subscription was triggered because of lack od resources on the server

The following status codes indicate that a subscription failed to be created:

Code Minor status Info
132609 Subscription creation failed because the statement is not supported Query is too complex
132610 Subscription creation failed because the statement is not supported Invalid statement for subscription
132611 Subscription creation failed because the statement is not supported Invalid set options for subscription
132612 Subscription creation failed because the statement is not supported Invalid isolation level
132622 Subscription creation failed because the statement is not supported used internally
132623 Subscription creation failed because the statement is not supported over the template limit per table

The following status codes are used internally and are classed as check kill and init modes:

Code Minor status Info
198656 Used internally: check kill and init modes Undefined info mode
198928 Subscription was destroyed Subscription fired because db was attached
198929 Subscription was destroyed Subscription fired because user was dropped
198930 Subscription was destroyed Subscription was dropped because of a resubscription
198931 Subscription was destroyed subscription was killed
199168 Subscription is active Undefined info mode
199424 Subscription initialized but not yet active Undefined info mode

Permissions

Requires VIEW SERVER STATE permission on server.

Note

If the user does not have VIEW SERVER STATE permission, this view returns information about subscriptions owned by current user.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

A. Return active query notification subscriptions for the current user

The following example returns the active query notification subscriptions of the current user. If the user has VIEW SERVER STATE permissions, all active subscriptions in the server are returned.

SELECT id, database_id, sid, object_id, created, timeout, status  
FROM sys.dm_qn_subscriptions;  
GO

B. Returning active query notification subscriptions for a specified user

The following example returns the active query notification subscriptions subscribed by login Ruth0.

SELECT id, database_id, sid, object_id, created, timeout, status  
FROM sys.dm_qn_subscriptions  
WHERE sid = SUSER_SID('Ruth0');  
GO

C. Returning internal table metadata for query notification subscriptions

The following example returns the internal table metadata for query notification subscriptions.

SELECT qn.id AS query_subscription_id  
    ,it.name AS internal_table_name  
    ,it.object_id AS internal_table_id  
FROM sys.internal_tables AS it  
JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id  
WHERE it.internal_type_desc = 'QUERY_NOTIFICATION';  
GO

See Also

Dynamic Management Views and Functions (Transact-SQL)
Query Notifications Related Dynamic Management Views (Transact-SQL)
KILL QUERY NOTIFICATION SUBSCRIPTION (Transact-SQL)