Custom metric running in transaction?

HugoKornelis

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.

Russell D

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).