Feature request: databases with non default options

[dmartesi]

Recently we get an application upgrade (app + databases) from a software house, then we immediately get worst performance. Of course they tell us "it's your SQL Server, your network" and so on... until I see the tag "(AutoClose )" in the database list of Azure Data Studio. In a perfect world, DBADash would show me a warning when it sees the AutoClose option on, because it is a mess. Is it possible to get warnings for AutoClose and other database options when they are different from default value?

Thanks

[DavidWiseman]

On the DB Options tab under the Configuration node DBA Dash will highlight any instances that have databases with the auto close option set (and various other DB level settings). From there you can drill down to see which databases.

[dmartesi]

Ok, it works as you described. But when I'm on the Home page, I see all my instances and the column "Database State" is green for all the rows. If I click on "view" I can see that "Auto Cose" is red but I would never have clicked if the background is green. Am I missing something? Or maybe I'm using a non default DBADash configuration?

[DavidWiseman]

The Database State check is looking for databases where the state is Recovery Pending, Suspect or Emergency. The Summary is for things that you would want to include as part of your daily checks so these states are important to highlight alongside missing backups, failing jobs etc. Auto Close or Auto Shrink is more of a potential misconfiguration. I don't think this should typically be part of the daily checks - more as part of a configuration review. I can see why you would want this to be more visible though given the issue with it changing during deployment.

As a workaround, you could create an agent job to fail if there are databases in the DBA Dash repository DB with auto close (which is picked up on the Summary page). Something like this in an agent job.

IF EXISTS(
    SELECT 1 
    FROM dbo.Databases D
    JOIN dbo.Instances I ON I.InstanceID = D.InstanceID
    WHERE D.is_auto_close_on=1
    AND D.IsActive=1
    AND I.IsActive=1
)
BEGIN
    RAISERROR('Auto Close is enabled on one or more databases',11,1)
END

Or a custom check could be used.

[dmartesi]

I added a custom check, using the code below:

USE [DBADashDB] GO

CREATE OR ALTER PROC [dbo].[DBADash_CustomCheck] AS -- Table variable used to ensure the output is in the required format DECLARE @CustomChecks TABLE( Test NVARCHAR(128) NOT NULL, Context NVARCHAR(128) NOT NULL, Status TINYINT NOT NULL, Info NVARCHAR(MAX) NULL, PRIMARY KEY(Test,Context), CHECK (Status IN(1,2,3,4)) )

/ Run your own tests and insert the results into @CustomChecks table. Test = Name of your custom test Context = Context where the test applies to. e.g. application name, job name, database name, server name etc. Status = 1=Critical (Red), 2=Warning (Yellow), 3= N/A (Grey), 4=OK (Green) Info = Any additional information you would like to include /

INSERT INTO @CustomChecks(Test,Context,Status,Info) SELECT 'Database AutoClose check','Database',2,'Warning on database ' + D.name + ' on server ' + I.ServerName FROM dbo.Databases D JOIN dbo.Instances I ON I.InstanceID = D.InstanceID WHERE D.is_auto_close_on=1 AND D.IsActive=1 AND I.IsActive=1;

-- return data in required format SELECT Test, Context, Status, Info FROM @CustomChecks

The SELECT statement returns 1 row, but Summary dashboard doesn't show me an alert, in this case a warning.

[DavidWiseman]

The custom check was designed to be deployed to the monitored instances rather than the repository database. You can still do the check against the repository database if the associated instance is being monitored. The stored proc needs to be deployed to whatever database the service is connecting to (typically master unless you specified an initial catalog for the source connection). The check will be associated with the instance that hosts the repository database.

[dmartesi]

Ok, I created the stored procedure in the master database of a monitored instance and it works as expected. Thanks... now is a perfect world :)