Monday, 10 November 2014

DynamicsPerf capture stat job failing

Recently I installed DynamicsPerf 1.20 on our production servers. After that I noticed that capture stat job was failing. A detailed look at the error log showed that it was failing at the step where it populates 'SQL Virtual Log File Information' and although the stored procedure does have try/catch block to handle exceptions it was still not executing the rest of steps.

It turned out that one of our SQL stored procedure was setting 'show advanced options' to 1 and after doing its business it was setting it back to 0. As a result when the capture stat job was trying to enable 'Ad Hoc Distributed Queries' it was getting an error.

I had to modify the capture stat stored procedure to check for 'show advanced options' and set it to 1 if not already set and at the end set it back to original value.

This posting is provided "AS IS" with no warranties. Use code at your own risk.