Regex filtering doesn't seem to work

patelrk

Hi

I have SQL Monitor Version: 2.1.0.238 on a 64 bit hosted environment. I want to stop getting alerts for any sql containing CHECKDB or BACKUP. I also want to exlude any process name called Microsoft Office 2003

So i configured the Long-Running query alert as follows:
Raise this alert when a query runs longer than:

-Only checkbox selected is the High one after 130 seconds
-Exclude queries with a SQL process name matching the following regular expressions: Microsoft Office 2003
-Exclude queries that contain SQL commands or objects matching the following regular expressions: BACKUP|CHECKDB

I did originally have each keyword on a new line but this didn't help either.

The alert sql text is shown below, which incidentally when i use a third party regex test tool called Expresso seems to match it correctly:

Process ID: 58
Process name: .Net SqlClient Data Provider
Host: A_SERVER
User: DOMAIN\user
Process login time: 19 Jan 2011 1:00 AM

SQL process fragment
exec sp_reset_connection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
USE [JustisDataStore]
DBCC CHECKDB(N'DATABASE_NAME') WITH NO_INFOMSGS

Chris Spencer

Hi

I don't think Microsoft Office 2003 would filter out this alert as the SQL Process name is .Net SqlClient Data Provider. Putting ".Net SqlClient Data Provider" in the SQL process name regEx box should filter out this alert without the need for any T-SQL Matching. However, it would filter out all other alerts with that process name which may not be ideal. I believe that ".Net SqlClient Data Provider" is the default process name if the Application Name setting is not explicitly specified in the connection string.

As far as I can see BACKUP|CHECKDB should be filtering out this alert. I'm currently attempting to reproduce this issue internally. Do alerts for sql containing BACKUP get correctly filtered out?

Regards
Chris

patelrk

Hi Chris

Sorry. I didn't realise that Microsoft Office 2003 and BACKUP|CHECKDB were AND's. I thought they were OR'd. That would explain why CHECKDB wasn't being filtered.

It would be nice then, to be able to set up multiple conditions on an alert not to be sent out as i can't see how i would do what i'm trying to do right now, that is filter out Microsoft Office 2003 OR BACKUP|CHECKDB.

Thanks
Rakesh

Chris Spencer

Hi Rakesh

Sorry they are OR'd. I was just treating each one on an individual basis in my last reponse. The process name (i.e. Microsoft Office 2003) won't work but the T-SQL filtering (BACKUP|CHECKUP) should. Therefore overall it should be filtering out the sql fragment shown.

I'll continue my investigation anyway.
Regards
Chris

patelrk

Hi Chris

I've taken BACKUP out of my T-SQL filtering so that it is now just CHECKDB.
The process filter still contains Microsoft Office 2003.

I will see what happens tomorrow morning after the backups complete, and provide you the feedback.

Thanks
Rakesh

patelrk

Hi Chris

Just checked the SQL Monitor email alerts for this morning and i have started getting the long running backup jobs through again, so yes, BACKUP filtering was filtering. Still getting the CHECKDB ones through even thought that's still specified in the filter.

Thanks
Rakesh

Chris Spencer

Hi Rakesh

Thank you for testing that. I'm glad to hear that the Backup filtering is working at least.

I'm still attempting to reproduce the issue with CHECKDB. I haven't been able to get an alert through where the sql fragment actually contains the CHECKDB command. I tend to see code internal to CHECKDB instead. But I'll keep working on it.

Regards
Chris

Chris Spencer

patelrk wrote:

exec sp_reset_connection -- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
USE [JustisDataStore]
DBCC CHECKDB(N'DATABASE_NAME') WITH NO_INFOMSGS

I still haven't been able to reproduce this internally. Is the above code in a stored procedure at all?

Regards
Chris

patelrk

Hi Chris,

Sorry for the late reply, i've been off for two weeks. It's a Check Database Integrity Task from a maintenance plan

Chris Spencer

Hi

Sorry for the delay also. That was just the information I needed to get some progress on this.

I think that I've managed to reproduce the issue you're getting. What happens on my system is that the Check Database Integrity Task is initially creating a low level alert with a SQL fragment as follows:

DECLARE @BlobEater VARBINARY(8000) SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB) FROM { IRowset 0x70C6840502000000 } GROUP BY ROWSET_COLUMN_FACT_KEY >> WITH ORDER BY ROWSET_COLUMN_FACT_KEY, ROWSET_COLUMN_SLOT_ID, ROWSET_COLUMN_COMBINED_ID, ROWSET_COLUMN_FACT_BLOB OPTION (ORDER GROUP)

The alert is raised initially as the above SQL wouldn't be filtered out with the BACKUP|CHECKDB regEx.

The alert is subsequently escalated to a medium or high level alert and the SQL process fragment is updated with new information i.e.

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
USE [RedGateMonitor21]
DBCC CHECKDB(N'RedGateMonitor21') WITH NO_INFOMSGS

If the alert was raised initially with this SQL fragment then it would I'm sure be correctly filtered out.

A workaround could be to disable the low and medium thresholds in order to prevent the alert ever escalating (or downgrading). This wouldn't prevent the alert appearing, it would just avoid the confusion.

So the issue seems to be that we re-fetch the SQL process fragment on alert escalation. I'm not sure whether this is a good thing or not, so would like your opinion on this if possible. Should we be updating the information on alert escalation?

Regards
Chris

Chris Spencer

Also changing the regEx to BACKUP|CHECKDB|BlobEater seemed to do the trick and correctly filter out the alerts.

Regards
Chris

patelrk

Thanks Chris

I did already have the low and medium alert thresholds disabled so i would never have seen the original sql fragment. That's why i couldn't figure out why the alert wasn't being filtered out.

I can't see this situation happening very frequently so am happy to add blobeater to my regex, but with the lack of sql fragment history as the alert escalates (if you don't have the low and medium level alerts selected), makes diagnosis quite difficult.

For example, in my situation where i have only High selected the alert chain would look a little like:

Alert level High ->
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
USE [RedGateMonitor21]
DBCC CHECKDB(N'RedGateMonitor21') WITH NO_INFOMSGS

Alert level Low->
DECLARE @BlobEater VARBINARY(8000) SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB) FROM { IRowset 0x70C6840502000000 } GROUP BY ROWSET_COLUMN_FACT_KEY >> WITH ORDER BY ROWSET_COLUMN_FACT_KEY, ROWSET_COLUMN_SLOT_ID, ROWSET_COLUMN_COMBINED_ID, ROWSET_COLUMN_FACT_BLOB OPTION (ORDER GROUP)


Thanks
Rakesh

Chris Spencer

Hi Rakesh

Hopefully the modified regEx will work for you too.

I've raised an enhancement request (reference SRP-3406) to get a history of SQL Process fragments listed on the Long Running Query alert details page. This should make diagnosis far easier in future.

Many Thanks
Chris