Transaction Notification Is Not Displayed When VIEW SERVER STATE Is Missing

Transaction Notification Is Not Displayed When VIEW SERVER STATE Is Missing

The Notification if execution contains open transactions option works as expected on some SQL Server instances, but does not display any notifications on others.

This behavior is typically caused by the absence of the VIEW SERVER STATE privilege for the current user on the affected server.

After a query is executed, SQL Complete determines the number of open transactions by querying the sys.dm_exec_sessions dynamic management view from a separate session.

Without the VIEW SERVER STATE privilege, SQL Server does not allow access to information about other sessions. As a result, the query returns NULL, and the notification is not displayed.

Diagnostics

The issue can be reproduced with the following steps:

Step 1. Open the First Query Document

Open a new query document in SSMS or dbForge Studio for SQL Server and execute:
Quote
BEGIN TRAN 
     SELECT 
           @@SPID AS current_session_id, 
                     (SELECT open_transaction_count    
                        FROM sys.dm_exec_sessions    
                        WHERE session_id = @@SPID) AS tr_count_in_session


Step 2. Open a Second Query Document

Open another query document and execute:
Quote
SELECT @@SPID AS current_session_id,   
         (SELECT open_transaction_count    
            FROM sys.dm_exec_sessions   
            WHERE session_id = <doc1_session_id>) AS tr_count_in_session


Infowhere <doc1_session_id> is the value of current_session_id returned in Step 1.
​
If the VIEW SERVER STATE privilege is missing, the query returns: tr_count_in_session = NULL

This confirms the root cause of the issue.

Resolution

Request the VIEW SERVER STATE privilege from the database administrator for the SQL Server instance where the notification is not displayed.

Info
Product Limitation

The current implementation requires the VIEW SERVER STATE privilege because the number of open transactions is obtained from a separate session.

Using @@TRANCOUNT within the same session is technically possible, but it does not always produce reliable results. For example, the value may be inaccurate during paged data retrieval or other operations that use additional internal queries.

    • Related Articles

    • CPU Metrics Not Displayed in dbForge Monitor for SQL Server

      Symptoms When using dbForge Monitor for SQL Server, you may observe that CPU metrics are not displayed for certain active connections. For instance, the CPU usage section might be empty or missing data, as illustrated in the example below: The ...

    • How to Connect to MySQL Server

      In the second article of the series uncovering how to get started with MySQL, we talk about the ways of connecting to MySQL Server. You can connect to MySQL Server using MySQL Client, dbForge Studio for MySQL, and MySQL Workbench. In this article, we ...

    • dbForge SQL Tools Missing After Installing or Updating SSMS

      When launching SQL Server Management Studio (SSMS) for the first time after installing dbForge SQL Tools, a brief initialization process is expected. This typically takes 1–2 minutes depending on system performance. If the tools—such as SQL ...

    • Devart SSIS Components Missing in Integration Services

      This article is for SSIS Data Flow Components. After installing Devart SSIS Data Flow Components, the components may not appear in Integration Services projects inside Visual Studio. This typically happens when the project configuration does not ...

    • T-SQL Debugger Cannot Start: SQL Debug Engine Is Missing or Not Registered

      An error occurs when attempting to start the T-SQL Debugger in dbForge Studio for SQL Server. The CheckDebugger utility reports that the 32-bit SQL Debug Engine COM object is not registered or that the ssdebugps.dll file is missing. Important ...