Can't turn off alerts for Auto Restore Jobs

Random Lengths

I run a job at 3am the restores all 27 of my dbs with drop on success. Weird things show up in SQL Monitor:

1. Page verification set to off alerts on 8 of the 27. Why not all 27? Some are Simple recovery model, other are full. No Common elements that I can see

2. Database Unavailable alert - marked as restoring, then online for 9 of the 27, again, why only 9? almost the same dbs as #1 above.

3. I tried restoring 1 db without drop, then configured the alerts for that _Restore db to disable all alerts. Then I dropped the db. This morning I got a new email alert from the server, but didn't show up in SQL Monitor 'Could not find database ID 37. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.' Why didn't this come through to SQL Monitor?

4. How do I hide any of the alerts for these _Restore db's?

chriskelly

SQL Monitor uses a complex data collection schedule to gather information about the entities it is monitoring. This has been designed to gain the best information at the most efficient rate, and tends to have less 'expensive' data being collected more often and more 'expensive' data collected less often. |This means that SQL Monitor will have the absolute minimal performance impact on the entities being monitored. For example, simple machine pings occur every 10 seconds, but the check for the fragmented indexes only occurs once a week (by default). This unfortunately does mean that some things are missed because the tool is unable to monitor every detail all the time.

Especially for points 1 and 2 I would think that the databases alerted on the databases that were in the appropriate state at the time that the relevant detail was checked. For example, when the database state was checked, 9 databases were in a state other than Online and the others managed to slip between the gaps because the change happened between the two consecutive collections.

Some further points to think about:
1. The Page Verification alert should fire when PAGE_VERIFY = NONE (or TORN_PAGE_DETECTION = FALSE for SQL 2000). Can you confirm whether this is true for all the databases?

2. For the 9 databases that were alerted on, are all the alerts now marked as Ended to reflect the Online status?

3. Has the database recently been renamed?

4. In the Configure Alert page you can select databases from the tree on the left hand side and disable alerts for them. However, some of the alerts work at the SQL Server instance level and so disabling these will disable them for the whole instance. This is why some of the alerts in the refreshed list for the database do not appear.
https://documentation.red-gate.com/disp ... ing+alerts

I hope this makes sense.

Random Lengths

I guess that makes sense.

FYI, all of these databases are Auto restores using SQL Backup 7, from most recent backup of each db with _Restore appended to the name, then dropped after success

1. Page Verify is set to CheckSum on all databases being backed up
2. Yes, all are ended
3. The name is the same after each restore, but the db is dropped immediately.

It seems, to make it stop, that I need to restore each db without drop, then drop alerting from the config for that db, then drop the db, and carry on. Will that work, even though the config won't be visible when the db isn't present? Is there a way to alter the config when the db isn't present? ideally I'd like to do it for one, then perhaps open a text file and copy past the other 9 or 27 dbs in, to save me the effort of actually restoring each db.

Random Lengths

I tried the restore of one of the database, then turned off the alerts, then dropped the database, to see if my idea would work. It didn't. I guess I'm not that surprised, each restore probably gets a new databaseID, and I imagine your configuration uses that ID to match when alerts are triggered. I can hear you say 'just ignore them' from here. I always fear alert fatigue.

Random Lengths

How do I clear the configuration settings for databases that are no longer present?

I tried my idea of restoring the db, then adjusting the config to ignore, then dropping the db, and it didn't work, but now I can't delete the config, and I'm getting this error message:

DESCRIPTION: Could not find database ID 37. Database may not be activated yet or may be in transition. Reissue the query once the database is available. If you do not think this error is due to a database that is transitioning its state and this error continues to occur, contact your primary support provider. Please have available for review the Microsoft SQL Server error log and any additional information relevant to the circumstances when the error occurred.

chriskelly

Is there any further information logged in the SQL Server error log?

Random Lengths

No further information is logged.