Query Notifications - sys.dm_qn_subscriptions

Article
02/28/2023

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

Share via