New release: SQL Monitor Version 12.0 is now available. Read more.

Custom metric running in transaction?

HugoKornelisHugoKornelis Posts: 36 Bronze 5
edited May 23, 2018 12:16PM in SQL Monitor
I am creating a custom metric to check if the service account has the Perform Volume Maintenance Tasks privilege so that Instant File Initialization can't be used. This metric uses xp_cmdshell to collect the information. To make sure it always works I query sys.configurations and enable (and later disable) xp_cmdshell if needed. The script works if I run it in SSMS.

When I test the metric from SQLMonitor, I get errors on the servers where xp_cmdshell is not enabled. The first error is "CONFIG statement cannot be used inside a user transaction", thrown by the RECONFIGURE statement. As a result, the xp_cmshell configuration is not changed, which then causes other steps to fail as well. I can reproduce this in SSMS if I encapsulate my script between BEGIN TRAN and COMMIT TRAN statements.

There is no reason for this custom metric to run in the scope of a user transaction. Is there any way for me to tell SQL Monitor to not start a transaction before submitting the custom metric T-SQL ?

EDIT:I can of course modify the script to check the TRANCOUNT system variable and issue a ROLLBACK at the start of the script. But I am not sure what that would break. And it's quite a dirty hack.
Hugo Kornelis
(SQL Server MVP, 2006-2016 // Friend of Red Gate)

Best Answer

  • Russell DRussell D Posts: 1,324 Diamond 5
    Accepted Answer
    I'm afraid its not possible to tell SQL Monitor to not start a transaction, no. The intention behind custom metrics is that we don't want the custom metric to change anything (which is reasonable as it is supposed to be a measurement of something straightforward).
    Have you visited our Help Centre?
Sign In or Register to comment.