Can't get SQL Watch 4.2 to Send Blocked Process Notifcations

General
1

Hi,
I am trying out SQL Watch for the first time (v4.2 - 4.2.0.28234). I am using SQL Server 2019 Dev edition but I cannot get the blocked process report to email.

There seems to be a mismatch between the report definition and the view vw_sqlwatch_report_fact_xes_blockers. This mismatch also appears to affect the Blocking tab in the dashboard with all the panels showing an error “Something’s wrong with one or more fields”.

All I have done to the installation is change the email address in action_exec field for action id -1 and set the action to enabled. Database mail is enabled and is verified as working with the command text in the action_exec field.

When I force some blocking to occur the app log has the following errors

usp_sqlwatch_internal_process_checks - Errors when processing Action (Id: -8) for Check (Id: -2)
usp_sqlwatch_internal_process_actions - Action (Id:-8) calling Report (Id: -3)|
usp_sqlwatch_internal_process_reports- Error when executing Template Report (usp_sqlwatch_internal_query_to_html_table), @report_batch_id: NULL, @report_id: -3
usp_sqlwatch_internal_action_queue_update - Incorrect syntax near ‘,’. (@queue_item_id: 1)

Thanking you.

2

Version 4.3 has fixed the Blocking tab in the dashboard. But the blocked process report is still broken. Any ideas, anyone see the same or similar behaviour?

3

Hi, sorry I missed your post.
I’ll have a look and get back to you.

4

Hi Colin,

Can you try this please:

;with cte_blocking as (
	SELECT *, rn=ROW_NUMBER() over (order by dateadd(ms,-blocking_duration_ms,event_time))
	  FROM [dbo].[sqlwatch_logger_xes_blockers] b
	  WHERE snapshot_time >= isnull((
	select '1970-01-01'
	from [dbo].[sqlwatch_meta_check]
	where check_id = -2
	and sql_instance = @@SERVERNAME
	),getdate())
)
select (select 
	'<hr>
<h3>Blocking SPID: ' + convert(varchar(10),c1.blocking_spid) + '</h3>
Database Name: <b>[' + c1.[blocking_currentdbname] + ']</b>
<br>Blocking App: <b>' + + c1.[blocking_clientapp] + '</b>
<br>Blocking Host: <b>' + c1.[blocking_hostname] + '</b>
<br>Blocking SQL: <table cellpadding="10" border=0 width="100%" style="background:#ddd; margin-top:1em;white-space: pre;"><tr><td><pre>' + c1.[blocking_inputbuff] + '</pre></td></tr></table></p>
' +
	( select char(10) + '<table border=0 cellpadding="10" width="100%"><tr><td style="width:25px;"></td><td style="background:red;">
Blocking start time: ' + convert(varchar(23),dateadd(ms,-blocking_duration_ms,event_time),121) + char(10) + '
<br>Blocked SPID: <b>' + convert(varchar(10),c2.blocked_spid) + '</b>
<br>Blocked for: ' + convert(varchar,dateadd(ms,c2.blocking_duration_ms,0),114) + '
<br>Blocked SQL: <table cellpadding="10" border=0 width="100%" style="background:#ddd; white-space: pre;"><tr><td><pre>' + c2.[blocked_inputbuff] + '</pre></td></tr></table></td></tr></table>'
	from cte_blocking c2
	where c1.rn = c2.rn
	order by rn
	for xml path(''), type).value('.', 'nvarchar(MAX)')
	 t
from cte_blocking c1
group by c1.blocking_spid, c1.[blocking_currentdbname], c1.[blocking_clientapp], c1.[blocking_hostname], c1.[blocking_inputbuff], rn
order by rn
for xml path(''), type).value('.', 'nvarchar(MAX)')

If it does work for you, you can update the [dbo].[sqlwatch_config_report] table, report_id = -3 with the new code.

Let me know how you get on.

5

Thanks. It works fine. Lists the blocking SQL as well as the actual blocked SQL in the Blocked SQL portion of the report but minor.

6

Apologies for that. You probably noticed the blockers have been redesigned recently and I’m making few more tweaks for version 5 so it’s currently work in progress.

If you have requests or ideas what you’d like to see in the blocked process report or how you’d want it to work I’m all ears.

Thanks