How do you use cloud databases? Take the survey.

Exclude Offline database from SQL Monitor

I have a database that is only online for one day a month to act as a source for finance reporting.  The rest of the time it is offline.  It appears that SQL Monitor is trying to access it every few minutes, and raises an error in the SQL Server log, even though I have disabled all DB specific alerting for that database.  Is there some SQL Monitor process that tries to enumerate all of the databases on an instance?
Tagged:

Answers

  • Alex BAlex B Posts: 1,052 Diamond 4
    Hi @BillFinch,

    Yes there are several processes that do this I believe.  Disabling the alerting doesn't actually disable the data collection (and so doesn't disable the queries that are attempting to run against that database).

    Is the error a Severity 21 error that you are seeing?  If not can you let me know what error it is you are seeing in the SQL Server errorlog?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • BillFinchBillFinch Posts: 3 New member
    Hi @Alex B ,

    Yes, it is a Severity 21 error.  It fires pretty much every 10 minutes.  Here is the text of the two consecutive errors as logged:

    Error 1

    Date        8/6/2021 9:03:39 AM
    Log        SQL Server (Current - 8/6/2021 9:03:00 AM)

    Source        spid80

    Message
    Error: 615, Severity: 21, State: 1.

    Error 2

    Date        8/6/2021 9:03:39 AM
    Log        SQL Server (Current - 8/6/2021 9:03:00 AM)

    Source        spid80

    Message
    Could not find database ID 9, name '<REDACTED>'. The database may be offline. Wait a few minutes and try again.


  • Alex BAlex B Posts: 1,052 Diamond 4
    Hi @BillFinch,

    Righto, that's something we've seen before where the new database configuration options sampler (which runs every 10 minutes) is having that error occur for some offline databases in some occasions. 

    As I understand it, from what the developers have commented on the internal issue SRP-13822 where we are tracking this, it is a SQL Server issue that is causing that error to appear (as it doesn't happen on all offline databases / in all cases) and they are considering whether and/or how to try and work around it in code.

    In the meantime, to prevent this sampler from running you will need to use the ConfigurationFileEditor.exe (located in the SQL Monitor install directory, C:\Program Files\Red Gate\SQL Monitor\BaseMonitor\Tools by default).

    1. Run the exe explicitly as an administrator (right click > run as administrator)
    2. Click "load a configuration file" (this should make a backup copy, but you  may want to also make a copy yourself)
    3. Choose the RedGate.SqlMonitor.Engine.Alerting.Base.Service.exe.settings.config (located in %ProgramData%\Red Gate\SQL Monitor)
    4. Scroll down to nearly the bottom and find the [Cluster].[SqlServer].[Database].[ConfigurationOptions] entry and check the box next to it, then click on the dropdown box that appears and choose "Disabled"

    5. Then click the Disk icon at the top left to save the configuration file modification
      ​​
    6. Finally restart the SQL Monitor Base Monitor service

    If you then wanted to revert the change you could either replace the modified file with the backup of it (after renaming the backup file appropriately) or you can use the ConfigurationFileEditor and go back and uncheck the box next to the sampler which will make it use the default setting (of collection every 10 minutes).

    I will update here again when I have further information on the internal issue SRP-13822.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • BillFinchBillFinch Posts: 3 New member
    Hi @Alex B ,

    Thanks for the detailed explanation and workaround.  I made the config change on the base monitoring instance responsible for the SQL Server where the offline DB lives and the errors stopped immediately.  I'll keep and eye out for your updates

    Thanks again!

    Bill
  • Hi @BillFinch,

    The team have now modified the query for the database configuration options sampler to exclude offline databases which should avoid this error occurring and this is available in version 11.2.16 which they've just released.  You can download it here: https://download.red-gate.com/checkforupdates/SQLMonitorWeb/SQLMonitorWeb_11.2.16.16124.exe 

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • pooyanismpooyanism Posts: 1 New member
    Hi,
    I have a same problem.
    Here is the RedGate Monitor Alert sending every 10 Minutes:



    However, I don't have any database_Id = 16


    Also, I don't want to make disable any alert from configuration.
    I reset RedGate Monitor Service, but Alert message still comming!
  • Alex BAlex B Posts: 1,052 Diamond 4
    Hi @pooyanism,

    The team have released version 12.1.22 this morning that makes a change to the cursor used in the query that is seeing this error, which will hopefully address this issue.  You can download here: https://download.red-gate.com/checkforupdates/SQLMonitorWeb/SQLMonitorWeb_12.1.22.29294.exe 

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • JoeHarkinsJoeHarkins Posts: 15 Bronze 3
    I just upgraded to 12.1.28 and this error is back.
    I dropped and restored a database and now every 10 minutes SQLMonitor throws the error similar to the above, referring to the database as though the name and id were the same. 

  • marclenmarclen Posts: 6 Bronze 1
    edited August 18, 2022 2:17PM
    I have the same Problem again.
    We do daily Clones of Databases and before delete the old ones.

    Now we got the same Error for many Clones (40Databases at Night) each 10 Minutes.

    Is there a Workaround for this?

    We use SQL-Monitor Version 12.1.29.33207

  • Currently running 12.1.15.26810 and also seeing this. We have an AG cluster in pre-live and I have dropped and recreated the db's which has spawned these errors. Will 12.1.22 address this? The experience of the folks above suggests otherwise...  
  • Hi @owain_gwynedd,

    It's possible updating will help as it has helped some users, but there are other users who describe a similar series of events (with AG and mismatch dbid) that are still seeing the issue even on 12.1.30, though we have not been able to reproduce the issue here locally yet.  What OS and SQL Server versions is this occurring with?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Alex B said:
    Hi @owain_gwynedd,

    It's possible updating will help as it has helped some users, but there are other users who describe a similar series of events (with AG and mismatch dbid) that are still seeing the issue even on 12.1.30, though we have not been able to reproduce the issue here locally yet.  What OS and SQL Server versions is this occurring with?

    Kind regards,
    Alex
    Hi Alex, it's SQL2019 15.0.4236.7 (x64) on Windows Server 2019 Standard 10.0 (x64) Build 17763. 
  • Ken_BKen_B Posts: 1 New member
    HI @Alex B 

    I am currently experiancing the same issue. 

    I have recently upgraded to 12.1.32.1452. Today we had dropped two databases and recreated them on the same instance. Since doing so we are recieving the above error message for both databases every 10 minutes. It looks like its trying to find the database by id, but the id has changed when we restored it. Is there a way to stop it looking for this ID?

    I tried the above mentioned config change but it continued to notify us. I have changed the alert to ignore severity 21 in the meantime, this has stop those notifications while i find a solution. 

    Thank you
    Ken
  • Hi @Ken_B,

    Were your databases a part of an AG?  And what SQL Server version is the instance they are on?  If it's not the SQL 2019 version Owain mentioned then updating may help.

    Restarting the target SQL Server instance seems to work.  We also had one customer who just said said:
    I now tried to rebuild and join the database with deleting both copies of the database before restore instead of restore with overwrite and the error is gone even within Redgate Monitor. It seems to be a bug in SQL
    I'm not sure if their prior case is similar to what anyone else had done and whether this may help.  This seems likely since the cursor used should be recreated each time the sampler runs.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • SireSire Posts: 7 Bronze 2
    Has there been any progress on this? We got this error now when we deleted and then restored a database (with a gap of perhaps a minute or two). We cannot restart the SQL Server because it's running in production.
Sign In or Register to comment.