Custom SQL Counter... but on a database level?

Good morning,

I'm looking at trying to find a way to do the following.

For arguements sake, lets say that I have a single table in every database on my server.  This table updates every 5 minutes with a datetime stamp via an SQL Agent task.

If, for any reason, a database becomes more than 15 minutes behind, I would like SQLDM to change the status of the server to "Warning" or "Severe" and, when i hover over it, I'd like it to say "Database X is Y minutes behind".  (Much as we have the alert for Table Fragmentation > 75% on database XYZ")

I know exactly the SQL query I need to do this, however, what I dont know, is how to actually create such an alert? 

Is it possible to create a custom alert on a database level?  (I've seen custom counters, but that only seems to be a single, numerical value at a server level)

Thanks

Andy 

  • As far as I know, there is no such thing as a per database custom counter. It is per instance only.

    The only possibility I see, is to create a query that loops through your databases and returns the MAX minutes the timestamp is behind over all databases (= single numeric value). So, at least, you get an alert on the instance level. You then have to connect to that instance with SSMS and find the affected database yourself.

    Cheers,
    Daniel

  • Hi Daniel, 

    That's pretty much the conclusion that I was having as well.  Or having an SSMS job poll results into a single table and trigger the alert based on that (sp_foreach_db isnt always reliable).

    Thanks for confirming tho.

    Andy

  • Another Idea:

    If the number of databases you need to monitor this way is relatively small, you could create a custom counter for every database and link them all to your instance.

  • This would work as well. You could even potentially create one custom counter first, export it, copy and modify the XML to have it applicable to different databases, then import the new XML that would be for the other databases. 

    I'd probably make sure that the name of the custom counter reflected the specific instance and database that it was targeted for so that when SQLDM raises the alert, there's no question as to which instance and database it applies to.