Turn on Blocking and WhoIsActive without dbatools?

We are testing sqlwatch and we aren’t using dbatools currently. Is there a way to turn on the blocking and WhoIsActive reports without using dbatools? I didn’t have WhoIsActive when I installed sqlwatch but do have it as an sp now.

And I don’t know how to translate dbatools to powershell.

Hi,

Blocking is captured via blocked process monitor as explained here: Configuration | sqlwatch

To collect WhoIsActive, providing you have installed the procedure, you just have to enable the relevant agent job.

Wow. I saw that blocked process in the Docs, but somehow I convinced myself that was a dbatools command. #sheesh

I didn’t see an agent job for whoisactive. At least none of the jobs were named that. Would you happen to know which job it is? I have all of them active but it’s still showing “No Data”

Thanks for your help and your patience - glad you didn’t reply with RTFM!

Haha RTFM - didn’t even cross my mind!

The job is called SQLWATCH-LOGGER-WHOISACTIVE but I actually dropped support for WhoIsActive in recent versions. I can’t recall which version exactly, was just trying to find a reference on GitHub but can’t. In summary, the justification was that WhoIsActive provides very little benefit with the way it was being used in SQLWATCH. There are other methods in SQLWATCH to collect the same information in a more reliable way such as Extended Events.
That’s not to say WhoIsActive isn’t good - it’s brilliant but just not what I needed. As an example, if you run it every 15 seconds to collect your workload, there could be a lot happening between collection points and you could lose a lot.
Having said that, if you really want it back, you can do. If you just give me your SQLWATCH version I will try work out what you need to do.

Nah, don’t worry about it. I just wanted to make sure we had a “complete” version for testing. We’re evaluating a test case to see if it will work for all our servers.

I appreciate the offer, but it’s not really necessary. I can’t remember the last time I even tried to use WhoIsActive so it’s likely not worth your time.

Thanks again!

No problem.
Please let me know the outcome of your evaluation.
If it does not fit - let me know why - I am keen to make it work for everyone so rather than just dismissing it, let time know I can make it work for you.

Thanks

Marcin,

Will do and thank you.

The only thing I wish for right now is a MySQL central repository. That would save another SQL license and a big chunk of change.

You can use a SQLEXPRESS instance as a central repo to save on buying another license. You’ll need to set up a scheduled task to do the retention cleanup and other Agent actions, but I got SqlWatchImport to work to get data into a SQLEXPRESS instance without issue. SQLEXPRESS does support data compression, so that’s another plus.

1 Like

Yep. And version 5.0 will have broker based scheduled retention and maintenance tasks so there won’t be any need to setup scheduled tasks. The ideas is that it will be all out of the box.

1 Like