csmitheu
I want to be woken up if something doesn't come back, not if it does or even if it has gone into limp mode.
Seriously you're looking at the wrong end of the problem.
Sure there are more possibilities, but these should all be covered with a complete fallback DR strategy i.e. if not identifiable cause X, Y or Z then assume the worse, snapshot everything for debugging later and fallback to a complete restore from scratch.
The trick is to make X, Y and Z self-healing and part of your architecture provisioning.
I get the feeling most people here haven't dealt with large deployments and multiple site redundancy, five-nines reliability requirements and extensive DR planning, because the above is pretty obvious to those of us who have.
Even with our company which has in in-house ops team and our own colocated kit, we expect process failures and restarts and plan accordingly. No sleep is lost even when something explodes at 2AM (other than for the ops team, who in this case you contracted out).
As someone who evaluated and eventually recommended against implementing MongoDB for a new product, I entirely agree with you. The Mongo implementation was shot after about three months of wasted time after failure in pre-production testing. It was rolled back at great cost to SQL Server with a light document-style abstraction as Mongo failed miserably on precisely what you said: reliability, consistency, scalability and storage management.
Proper relational databases (PostgreSQL, Oracle, SQL Server in my scope of experience) can take an absolute incredible amount of punishment without breaking any guarantees. The relational bit above is optional but it is at least something you can choose to use or not.