SQL Monitor - Lightweight Last Data Change

JasonL

Hello,
I'm looking to potentially use SQL Monitor for a lightweight data change monitor. The goal of the effort is to alert a support team when a given database has not data inserted within a certain period of time. Any ideas on if this can be done with SQL Monitor, and if so how?

Thanks,
Jason

Alex B

Hi @JasonL

It may be possible to do this using a custom metric, but the logic might need to be a bit convoluted.

You would need to have a T-SQL query that would give you a numeric result that represents whether there is data being inserted into the database and you would then target this at the specific database in the "Choose databases to collect from" section after selecting the specific instance the database is on in the "Select instances to collect from" section.

You would want to calculate the rate of change and have the alert fire for when rate of change was below 1 (i.e. 0 or not being added to) but this would then also alert if the rate of change was negative (i.e. data being removed instead of added).

I'm not sure what query would help in this case though - maybe you could query sys.master_files to get the database size? If this was a specific table you wanted to see if data was added to in the database it could be something like "SELECT COUNT(*) FROM tableName" perhaps.

I hope this helps!

Kind regards,
Alex