Can't turn off alerts for Auto Restore Jobs
Random Lengths
Posts: 43 Bronze 2
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?
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?
Comments
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.
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.
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.