I'm working on a system that uses this mechanic.
You have X points in your 'stress pool'. Casting costs points from it. It can be depleted by things that do 'sanity damage' as well.
When you have fewer than half your total stress points left, you take a minor penalty to actions and defenses (you are rather unsettled) and a bonus to fleeing, melee attacks and strength checks. The penalty/bonus increases when you have fewer than one quarter (you are freaking out).
If you go below zero, you then roll on an 'overload' table if you did so with spellcasting, or 'breakdown' if it was caused by sanity damage or something else. The amount below zero is added to your roll on the table.
Overload is basically a wild magic table, with severely detrimental effects toward the high end. The highest possible result is 'you explode with magical energy, obliterating your body and dealing damage in a radius'.
Breakdown ranges from temporary mental problems and penalties to stacking, long-lasting psychosis of various types. The highest result there is death by brain aneurism or something.
Spells don't have fixed durations in my system (as of yet). All spells that last more than one round last for as long as the caster maintains them. Each such spell has a maintenance value usually based on its level. When you take stress damage, you also take additional stress damage equal to the total maintenance values of all spells you are maintaining. It can add up pretty quickly.
This does mean that using fear and insanity effects on a mage that's running a huge lightshow can make him explode.
I haven't done any work toward a d20 version of this, but it could be done.