For SQL server, is there any way to monitor auto create statistics option other than custom SQL?

Hi,

I am trying to create a monitor for all the SQL server DBs we have to alert if different SQL DB options are ON/OFF.

Like if the auto create statistics option is OFF, it should raise an alert.

I can easily setup a monitor using simple SQL as: SELECT COUNT(*) FROM sys.databases WHERE is_auto_create_stats_on = 0;

Then alert if result is greater than 0.

But this way I won't be able to inform which database has auto create statistics option OFF. And this makes it almost useless from DBA's point of view.

So I am trying to find if there is a way to inform (in message) which database or databases are having auto create statistics option OFF.

I tried to modify the above SQL to add second column with names of databases in concatenated string form but looks like it is not an easy task (or I don't know SQL enough)

So, I wanted to check if there is any provision in SolarWinds to monitor on this property. May be using App Insights for SQL?

Find more posts tagged with

appinsight_for_sql

sql server db

custom sql query

Accepted answers

j_a_catlin

This should take care of what you are looking for using the SQL server user experience monitor you were probably already basing the monitor on.

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + Name

FROM sys.databases where is_auto_create_stats_on = 0

SELECT COUNT(*), @names FROM sys.databases WHERE is_auto_create_stats_on = 0

All comments

j_a_catlin

This should take care of what you are looking for using the SQL server user experience monitor you were probably already basing the monitor on.

DECLARE @Names VARCHAR(8000)

SELECT @Names = COALESCE(@Names + ', ', '') + Name

FROM sys.databases where is_auto_create_stats_on = 0

SELECT COUNT(*), @names FROM sys.databases WHERE is_auto_create_stats_on = 0

300

Works like a charm!

Thank you so much.

leah.brooks

Hello,

We are trying to make a SQL statement that sends an email alert. Here is the statement that we have, that looks at a table for the value.

SELECT count

FROM [Server].[our_database].[dbo].[our_table]

How do we set up an alert that will send an email when the value is greater than 0. We are stuck on the exact syntax to use in the statement. Also, how do you use the returned value to send the alert?

Thank you!

300

I use the "SQL Server User Experience Monitor" and put the SQL in the monitor.

Then After assigned node to this monitoring template, we have created custom property 'emailids' at template level.

I don't know how it's setup at back end (it was done before I joined in) but we can provide email ids separated by simple comma (,).

So whenever the template goes in alerting state, the email ids used in the custom property 'emailids' will get email generated by solar winds.

Hope this helps a bit, as I couldn't give details of how it was setup on backend.