How do you use cloud databases? Take the survey.

Custom Alert Not Clearing

On the lastest version ( and I've noticed that at least one custom alert is not clearing: 

The metric is collected in each user database and runs the following query (returning 1 if the database is not encrypted with native TDE). I can test the query on the database in question and it returns the expected value (0) but it seems as if the query is not being re-run (despite being set to run 1/hour) and the alert based on the metrics continues to show as active.

SELECT COUNT ([d].[name]) AS [UnencryptedDatabases]
FROM [sys].[databases] AS [d]
LEFT OUTER JOIN [sys].[dm_database_encryption_keys] AS [dek] ON [dek].[database_id] = [d].[database_id]
LEFT OUTER JOIN [master].[sys].[certificates] AS [c] ON [dek].[encryptor_thumbprint] = [c].[thumbprint]
WHERE [d].[name] NOT IN ( 'master', 'tempdb', 'model', 'msdb'
  , 'sqlmgmtd', 'localadmin' )
AND  ( [c].[expiry_date] IS NULL
OR [dek].[encryption_state] IN ( 0, 1, 5 ))
AND [d].[name] = DB_NAME ()
AND [d].[state_desc] NOT IN ( 'RESTORING', 'OFFLINE' );

Best Answer


Sign In or Register to comment.