Read some of the entries in Intruders: Encounters With The Abyss at night, all alone, and try not to feel terrified. It's no small reason why it's considered the number one sourcebook by the Mage community.
Grigori are the disembodied souls of Sleepwalkers, the ultimate agents of the Panopticon. Disembodied souls of Sleepwalkers that, it should be mentioned, are still alive... and completely unable to return to their bodies at all, or control their astral selves. All they are now are prisoners of an Artifact of Doom, their living selves kept in convenient storage so the Seers are never far away from the stream of observations they are forced by it to utter, without even the solace that starvation will eventually claim them-the Artifact supplies their body's needs, no matter how much they don't want to.
The fate of the Hollow Ones is even worse - they were Sleepers (not Sleepwalkers, Sleepers, utterly ordinary people) who were abducted by the Paternoster and thrown to the Custodian, a being from the Lower Depths who eats individuality. The... thing that is left over looks like a person, but is completely unable to retain memories or personality of its own without magic, and without regular infusions will rapidly degrade to a vapid husk once again. At least the Grigori can die knowing they're finally free. For the Hollow Ones, the self is... gone.
For more Abyssal goodness, the Hildebrand Recording from Grimoire of Grimoires. Some poor sucker ends up holding a recorded seance, and we get to read about what happens when an Abyssal intruder picks up instead. And yes, there are snippets. The recording itself allows mages to perceive things through the filter of the Abyss (as in, twisted and wrong), but it's also an object of fascination for mortal occultists, who will do anything - anything - to get their hands on it...
And one of those occultists? A Russian mob boss who used to play it for laughs, and once cut off the limbs of people who offended him.
Also, the Hildebrandt Recording is explicitly stated to be an impossibility. Hildebrandt himself was an ordinary man who should never have been able to summon the being at all. He used ordinary equipment, which should have been unable to record its sounds if he did. And for the recording of the seance to become a genuine grimoire should have been flatly impossible. It is, quite literally, a thing that should not be.
The recording itself has a pair of rotes on it that can be decoded with the proper spell. One of these is a spell that lets a mage detect the presence of the Abyss' influence; Unfortunately, the way the caster perceives said influence is pure distilled Nightmare Fuel: Inhuman whispers, moans of suffering, and general feelings and images of pain and horror. The writeup on the spell says that a mage who uses it is unprepared the first time they use it, they could end up picking up a derangement. On top of all that, the spell is also in-universe Paranoia Fuel: Mages who use it regularly get too scared to go through life without it activated.
Perhaps the worst thing about the Hildebrandt Recording is that possessing it is actually sort of useful to the Awakened, because it eats Paradox, lessening its effect on the mage who possesses it; anything that does this is worth its weight in gold. The problem is that it doesn't actually quash Paradox, it just makes it go... somewhere else. So it's less that it stops Paradox so much as it makes it somebody else's problem.
The Tremere Liches, as a base concept-immortal soul-eaters-are pretty horrific in and of themselves. Then you learn their backstory, and realize the initial ones were made accidentally, and you start imagining how utterly horrific it is to be one...
As of Left-Hand Path, the Tremere have become a whole new kind of Nightmare Fuel-they knew exactly what they were getting into when they tricked the vampires into destroying their souls, and they don't care, because they've become living maws of the Final Watchtower, meant to devour enough souls and internalize enough Reaper Legacies so it may manifest and remake the world in their image. And if your soul is one of them, well, tough noogies.
What are the Lower Depths? What place in the cosmic order does it hold? And why are even Abyssal intruders afraid of things that come from it?
How about the Inferno, for one? And it's only a single world within them?
It's strongly suggested that the Lower Depths are reflected realities that all lack one Arcana or another. Just imagine a world without Space... or Mind... or Life... or Death...
Night Horrors: The Unbidden is supposed to be a catalogue of this. And it works quite well, with something for everyone. Mad mages, enchantments gone wrong, gruesome creatures of living magic, malevolent ghosts, corrupt spells, and more.
One example? Alecto, an ananke (a magical construct designed to fulfill a specific purpose) who believes her purpose is to usher people on to their destinies. However, something or someone maimed her in the distant past; she manifests with a broken wing and bleeding, empty eye sockets. And she doesn't realize she's broken; she can't actually remember her real purpose, she just infers it from her powers. She might see an arguing couple, one that would make up in due time, and believe that their destiny is to separate. Her grasp of Fate magic is near-perfect, but it's being filtered through the mind of a severely-damaged being that can't grasp how damaged it is. She drives people to despair and suicide, never realizing she's making mistakes, and in fact incapable of realizing her own flawed judgment. A Woobie, Destroyer of Worlds who highlights just how dangerous magic is if you're not sane enough to control it.
Everything in the Anima Mundi. Everything. EVERYTHING.
The Omphalos, which is essentially a combination of an astral roadblock and warning sign indicating that metaphysical shit is about to get real. Breathing in the mists surrounding it causes you to become conversationally fluent in the High Speech - which shouldn't be possible, because the language is metaphysically broken.
The Spire Perilous, which was the original connection between human subjectivity and the soul of the world. The collapse of the Celestial Ladder severely damaged it, and one of the Aeons is attempting to finish it off.
The Citadels of the Aeons. Some of them live in castles built to resemble a giant in the fetal position, dying of starvation. One is a braindead king with two dragons growing from his shoulders with a personality best described as "Satan." And then there's the Other, Ambassador to the Abyss. There is something very wrong with the Other.
And worse? The wrongness happens in spite of him. As shown in Left-Hand Path, the Other won't harm anyone, nor will he allow others to come to harm. He won't tempt people into following the Abyss, and he mocks the Scelesti who try to worship him in person. And he apparently genuinely regrets the nightmares he causes in others. There is something even more disturbing about that.
How about the very existence of Joe "Blood of the Lamb" Beal? How about we start with his Religious Horror Awakening, wherein he grew fascinated with his own cannibalistic Blood Magic orientated interpretation of Christian doctrine, perceiving Jesus Christ as a cannibalistic Apex Predator, which eventually prompted him into a dream in which he tortured and ate Jesus? A Thrysus who turned to Abyss-worship because he found the Thrysus philosophy too stifling, he uses Life magic to aid him in psychologically and physically torturing anyone who catches his eye. For no greater purpose than he believes all other life is weak, and so that makes them prey who he can do whatever the hell he wants to. He takes everything you could possibly imagine as terrifying about a Serial Killer, and then makes it worse by adding in mastery over the magic branch that allows him to physically shape and reshape himself or others in whatever fashion he desires, plus basic Mind Control spells that target the most primal parts of the brain. Really, the fact he worships the Abyss is the least frightening thing about him.
While the Blood of the Lamb is terrifying, the worst thing about him is arguably what he represents. Apparently, being a totally, batshit insane sociopath with an obsession with cannibalism is absolutely NO barrier toward the kind of self knowledge and understanding that leads to Awakening. Imagine Jeffrey Dahmer, Ted Bundy, Ed Gein in the world of darkness, and imagine how many serial killers have awakened and slipped through the cracks? Imagine all the people out there who aren't Scelesti and in theory are tolerated by their Consilii? Joe Beal is scary, but the fact that people like him can and probably do exist, and almost certainly have achieved some truly god-tier reality warping powers is paranoia-fuel to the max.
It's actually somewhat confirmed that there are other insane and sociopathic mages out there. Cameron Mueller from World of Darkness: Asylum is essentially a serial killer who uses little paper fetishes to imprison the ghosts of his victims. The book implies rather strongly that he either is on the verge of, or has already Awakened.
The Cult of the Doomsday Clock deserve special mention here. They're a Men in Black style doomsday cult that systematically finds disaffected, young Moros and twists them into time-manipulating monsters. They were spawned from the paradox of a battle between two archmasters of time, and are led by Abyssal monsters who are basically sentient, self perpetuating time paradoxes. The final attainment for the Legacy involves traveling to the past and killing yourself, becoming a living, Abyssal paradox manifestation of YOURSELF. Oh, and they have such control of time, that they can bounce back whenever they die, Prince of Persia-style, so to kill one, you have to make a situation so lethal that they die for each point of mana they have stored. They even have almost literal time bombs that annihilate things in the past, present and future, completely erasing them from the time stream, and their ultimate goal is to do that to the entire universe.
The Jnanamukti philosophy is a terrifying combination of The Fundamentalist and Omnicidal Maniac wielded by a full-fledged Reality Warper. They're an order of fanatics dedicated to a philosophy built on Fantastic Racism, magocrats who blame the Fallen state of the world entirely on Sleepers and who want to restore the Supernal world. Which they figure can be achieved if they wipe out civilization as we know it, drastically cull humanity's numbers, and annihilate all non-Mage supernaturals. They want their paradise so badly they're willing to completely destroy the world as we know it in hopes that this might give it back to them. And they could do it...
Really, mages themselves are scary beyond reason. They start as "mere" Reality Warpers, even at Gnosis 1, and they grow increasingly more powerful as they ascend. Archmages, although still constrained by some meaningful limitations, have gone beyond that title to the rank of Physical God; for these mages, reality is so much putty they can play with as they like, even if it goes awry sometimes — there's a passage in passing in Imperial Mysteries, the Archmage splatbook, where one Archmage makes a comment implying that they made Christianity the dominant religion in Western civilization by accident. They can't even enter the mortal world without risking causing ripples that could potentially unravel the world. Really, mages make a huge lie of the idea that Humans Are Special if you take Imperial Mysteries as canon; everything that generations of Muggles have accomplished can be undone by the whim of just one Archmage. Just try to wrap your head around that kind of power...
Making things worse is the underlying philosophies that Mage is built on; in essence, humanity should be a Witch Species, but the vast bulk of the species has been artificially blinded to the higher world around them. There's a subtle feeling of Fantastic Racism when mages contemplate Sleepers, but looking at the difference between what both are capable of... there's a sinking feeling in the depths of your subconsciousness that mages are justified in it. Really, when one takes into account the abilities of even a moderately trained mage in contrast a Sleeper, one could quite believably justify mages believing that Sleepers aren't human, they're just animals that look human, and acting accordingly.
You know who I find scarier? The guys in the Supernal Realms. I don't remember they're names, but the Seers to the Throne worship them. Yeah, they're immortal, and obscenely powerful by ARCHMAGE standards. Worse, they're selfish enough to cut off the rest of humanity from magic, and they're ancient. That might not sound all that bad, but people in the past had a much different sense of morality. Witch hunts( which the Seers probably caused), slavery, and of course, classism. And sexism. Just a few hundred years ago, if a man slept with a married woman, he could be fined, for "damaging the husband's property". And that was a marked improvement from what came before. So yeah, people in the past, not good. People in the past so horrible other mages decided they needed to knock it off, much, much worse. Also, these mages created everything in the Abyss. It's ALL their fault. Are these the people you would trust with Phenomenal Cosmic Power?