a small (i.e. low-powered) tube amp with an inefficient speaker will get you there. look for old Epiphones or Gibsons (although Gibsons are starting to attract attention now). to get what i consider real tube overdrive sounds, you gotta flog the power tubes.
my ancient Epiphone has 6BM8s in it, which (if i understand correctly) serve as both preamp tubes and power tubes. it will grind at acoustic volume with flattop pickers if you turn it toward the wall or otherwise position it so they're not in the direct blast. i often jam with bluegrass guys in living-room situations this way, trying in vain to convince them that under some circumstances, the Tele IS a bluegrass instrument!
the point is, if you're two rooms away from the sleeping kids, playing in a fairly dead space (carpet, drapes etc.), you could honk away without waking them — assuming they're already sound asleep.