Determine ratio of explicit to autocommit transactions on SQL Server table?

Question

Problem: To solve data consistency on one of most important tables in our system, all writes will be covered with explicit transactions (appropriate to what given stored procedure / business application is doing). However, since there are many sources of writes, it would be best if we had numerical measure of progress.

Ratio of explicit to autocommited transactions on that particular table would be one such measure. If after all the rework, there are still autocommit transactions in stats, we have proof that we missed some procedure or application (or manual process).

How can SQL server be queried for such information?

Answer 1

As mentioned by @lptr you can use sys.dm_tran_session_transactions to check if a user transaction was opened.

CREATE OR ALTER TRIGGER dbo.check_tran
ON dbo.YourTable
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

IF (SELECT is_user_transaction FROM sys.dm_tran_session_transactions WHERE session_id = @@SPID) = 1
BEGIN
    INSERT Audit_transactions (host_name, user_name, initial_batch)
    SELECT HOST_NAME(), SUSER_SNAME(), CONCAT(b.parameters, '  ', b.event_info)
    FROM sys.dm_exec_requests r
    OUTER APPLY sys.dm_exec_input_buffer (@@SPID, r.request_id) b
    WHERE r.session_id = @@SPID
END;

sys.dm_tran_session_transactions requires VIEW SERVER STATE permissions, so if the user does not have that then you may need to sign the trigger with a certificate of a user that has that permission.