Not long ago, a crack team of SQL Server experts was flamed for a crime against database normalization they didn't commit. These men promptly escaped from the taunts of academic relational theorists. Today, they survive as website editors and software evangelists. If you have a problem, if no one else can help, and if you can find them, maybe you can hire the DBA Team.
Based on a true story…which, as we all know, means "almost entirely made up"…
Scene 1: Bad Santa
December’s first snow flurries had quickly turned the landscape into a blank, white canvas. Phil Factor trudged through the snow up toward the hospital entrance. He was uncomfortably warm beneath his red velvet costume and heavy sack of gifts, but the cold winter air was reflected by the ice in his soul.
Sometimes, playing Santa for the children's hospital was a penance, and the obligatory joviality a strain. Today, he was feeling like a particularly Bad Santa.
"Blooming Christmas!" he muttered. "Couldn't someone else take on the role for once?” Every year, he grew to look inevitably more and more like Santa, no longer having any need for the false white wig and beard, nor for the cushion stuffed down his shirt.
Every year he pleaded for someone else to do it, and every year failed. "The children so love it: even the doctors and nurses look forward to your annual visit!" he was always told.
He paused, about to enter the hospital, when a figure to his left caught his attention, with a pale almost ghostly complexion, slightly unshaven, pacing back and forth outside the building opposite, hand moving repeatedly and nervously to his forehead.
Phil hesitated, remembering the words from Good King Wenceslas:
Hither, page, and stand by me
If thou know'st it, telling
Yonder peasant, who is he?
Where and what his dwelling?
He knew he shouldn't get involved, but years of experience meant he could spot a DBA in distress from 50 paces, and yonder peasant's agitation shone out like a beacon.
Good Santa kicked in. He let the hospital door swing back shut and crunched across the street.
Scene 2: Relational protection
Fabian stared thoughtfully at the report, sighing. Finally he flipped the folder onto the table, leant back in his leather chair, sighed, and let his gaze settle on the gold crest of the Relational Protection Agency, high up on the paneled wall.
"So…you managed to let the DBA Team escape from SQL in the City in Seattle, even though they were trapped in a room, giving a presentation?"
"Steve and Grant are slippery characters," admitted Celko, "and they have a shadowy support network."
"Speaking of shadowy, slippery characters, did you run across our arch denormalizer, Phil Factor?"
"No. We've all heard the wild stories of what he can do to a database, but at this stage we're fairly sure they're apocryphal. Phil Factor doesn't exist" said Chris, flatly.
Fabian peered once more at the face-sheet of the file in front of him, then flipped it across the desk. "So if Phil doesn't exist, how come he manages to dress up as Santa for his local children's hospital?"
"You're kidding? Legend has it that when doing database audits, he has a death-stare that numbs his victims at a range of 30 meters. It would be a bloodbath."
"Evidently, he gets asked back every year. I suspect he has a human side." Fabian carefully put the face-sheet back in the file. He paused.
"Now, since it's Christmas, I have a very simple task for you. Get to the hospital. Get Phil Factor. Pull him in for interrogation. It can't be hard: he'll be the one in the red outfit and a white beard."
Celko and Chris exchanged glances, stung by the sarcasm. Fabian closed the file and pushed it away. He tried Phil's death stare, but it glanced harmlessly off Celko's pate.
Scene 3: LUN done gone
"Thank goodness you're here at last! I tried to call you…there's been an accident…the LUN…it was just routine maintenance but it's gone and I don't even know how it happened, but oh man this is bad! The main database is down!"
Sander, the senior DBA, put down his coffee cup and gulped. He hadn't understood much of what DBA Ken had said, but he'd caught the name of the database that was down. It was the mission-critical database of the hospital. It couldn't function without it.
"Can you translate that into human speech?"
Ken took a deep breath. "Our SAN Admin accidentally deleted the LUN, the logical drive. Not just any old LUN, the one holding the transaction log file of the main database!"
Sander quickly checked the status of the main database and sure enough it was in
RECOVERY_PENDING mode, due to the missing transaction log file. "OK, no panic. We can restore last night's backup. There will have been little activity on the database since then. If we act quickly we can sort this out before anyone even notices, and with almost no data lo…"
Sander quickly checked the status of the main database
Keep an eye on your servers wherever you are with SQL Monitor
Sander stopped in his tracks, as he noticed Ken shaking his head miserably.
"Already tried it. A test restore of the full backup failed. I think it's corrupt. Can you believe it?! I can't. Maybe it's all a dream."
With those words, Ken smashed his head hard against the office wall. Twice. "No, it's not a dream so I guess we're stuck with the situation!"
"Calm down, Ken!" cried Sander, alarmed. "You're acting crazy. We can fix this!"
"You don't know the half of it. Did I mention that when I went outside to try to call you, I bumped into a guy dressed as Santa Claus, who claimed to be Phil Factor? He offered to help, and he's contacting the 'DBA Team'!"
Sander shook his head. "It sounds like we're sharing the same nightmare."
Scene 4: The Scary DBA achieves spiritual peace
Grant spluttered from the heady scent of jasmine flower. His meditation group were positioning themselves for their early morning meditation session, incongruous in fluorescent Lycra.
Outside, snowflakes drifted heavily in the wind, but inside all was warmth and calming wallpaper-music. Under his breath, Grant muttered the now-familiar words of his mantra – D-B-C-C-Check-D-B - and felt the deep calm gradually possess his body, building the internal energy of his life force. Then his cell-phone went off.
"Grant? It's Phil. Not disturbing you I hope? Still busy defending your servers against crazed zombie developers?"
Grant noted Phil's belly laugh, but in his heightened spiritual state refused to rise to the bait, instead remaining focused on the deeper spiritual truths of life.
"There are no zombies here, Phil. Just peace and understanding," he grunted, curling himself into the lotus position.
"Well, pack up your jasmine flowers and get into town now, because we've got some guys who are in a deep hole with a database and they need our help."
"Ah, Phil: all these poor souls, so bereft of spiritual values. What is the nature of the trivial problem upon which they misguidedly focus their life-force?"
"Their transaction log has gone AWOL, the database is down, and the worst thing is that these guys took backups without verifying them properly and now can't run a restore…"
Grant flinched involuntarily, accidentally kicking over a ceremonial bowl of rose petals. His fellow meditators looked up, startled.
"…wait, now…don't…make…me…angry. Are you saying they didn't check their backups by restoring them?"
"Afraid not, and it's come back to bite them. The hospital can't function without this database. They don't know what to do."
"I know what I'm going to do," Grant snorted "It's clobbering time!"
Scene 5: Steve maintains a straight edge
Steve smiled in satisfaction. Things were going well, and before long the Bummer cart would be working once more. He was picking the shavings from his shirt-of-many-colors when his phone went.
"Deep in preparing your presentations for the next SQL in the City, then?" It was Phil. He could tell from the ironic laugh. "Listen," he continued "there is a DBA in trouble."
"Phil, it's Christmas. I'm dressing a felloe."
"Fine by me. I'm broadminded."
Steve sighed, "I'm fixing a wooden wheel for my Horse's cart. I’ve got a lime felloe blank secured on the workbench. Once I’ve got that bellied out and dressed, there’s just the Chims to do.”
"Oh hell, and there was me preparing to be all modern and understanding. Look it's not just any database, it's the main database that runs affairs at our hospital, the children's hospital."
Steve stared moodily at the half-finished felloe-belly. "K. I'll be there. You know, I used to have a life before I joined the DBA Team."
"C'mon misery-boots. Season of goodwill and all that. Anyway, you always enjoy it when you get there!"
Phil hung up before Steve could think of a rejoinder.
Scene 6: Tidings of no comfort or joy
Phil, resplendent in red, with his festive bobble hat, strode down the hospital corridor with Steve and Grant.
"Now Grant, Steve…Grant especially," cautioned Phil, glancing nervously at the baseball bat that Grant was swinging causally at his side. BUD he called it, in honor of those unfortunate DBAs who had trouble Backing Up Databases. "I know they've got themselves into a bad spot here, but we need tact and diplomacy. These guys are my friends, even though they insist on me being Santa every year."
"Hey guys!" shouted Grant, cheerily waving his bat as they entered Sander's office. "BUD here would like a quick word with the DBA who is having trouble verifying his database backups, if that's OK?"
"What Grant means is, if you need any help at all, the DBA Team would be honored to assist!" interjected Phil, to Grant's visible disappointment.
Sander stared at them agog, until finally breaking the uncomfortable silence, gesticulating in amazement at Phil. "Wow, I didn't really believe you, Ken, but it really is Phil Factor…I think…and with the great Steve Jones!" Sander shook Phil then Steve warmly by the hand "And yes…the…err…scary DBA." Sander approached Grant but hesitated.
"Look the 'Scary DBA' thing is mainly just a front. I very rarely inflict actual, physical harm…" There was a tense pause "...nowadays…" Grant smiled indulgently. "But, just for me, let's hear how you got into this spot of bother."
Sander and Ken brought them up to speed quickly on the accidentally deleted LUN and the apparent problem with the most recent full backup.
"And you don't perform any proper backup VER-I-FIC-ATION!?…" interjected Grant, each syllable exploding like a bomb in the room as he started to pace up and down.
"We do test our backups periodically!" said Sander "but…with this new Patient Tracking project taking top priority, and almost all our time, we've not managed to get it automated."
Grant winced. "I know, I know," Sander sighed "That decision has proven to be a big mistake."
"OK, but a critical database like this must be backed up to within an inch of its life…differential backups, frequent log backups," mused Steve "As long as you have the previous good full backup and a complete backup chain…"
Sander and Ken squirmed visibly. "Well, you're right of course but, you see," spluttered Sander, "there've been a few issues with the SAN recently and, while we do have a job that regularly copies the log backups to a separate location…initially they are being saved temporarily to…errr…the same LUN as the transaction log itself…"
"Which got accidentally deleted," wailed Ken. "And we lost some of the backups!"
Grant's agitation and pacing increased, his face reddened, and he started muttering some strange mantra under his breath. Steve put a consoling arm on his shoulder "That's right old buddy, think calming thoughts. Don't suppose anyone has a spare supply of jasmine? Or lavender might do, in a pinch?"
Scene 7: Get Santa
Celko and Chris, dark glasses and black uniform, approached the hospital security desk.
"Morning gentlemen, what's your business on these premises?" asked the guard, cautiously.
"We are Federal Agents, working under the jurisdiction of the Relational Protection Agency." Celko flashed a warrant and badge. "Are you aware that you're currently harboring on these premises a fugitive from Relational Justice, name of Phil Factor?"
The guard blinked. "Wanted for what, exactly?"
Chris laughed bitterly, "We have a list as long as your arm!"
With that, Celko flipped open a weighty looking charge book. "Use of self-referencing tables to represent a hierarchy, use of table aliases that lack clarity or meaning, inappropriate use of hints to enforce
JOIN behavior…" Celko raised an eyebrow at the guard "Surely I need not go on?"
The guard sighed "You lost me at that bit about use of self-something tables. Is he in the furniture trade?"
"Far from it. It is imperative that we apprehend this person quickly. If he, or his notorious confederates, destroy the relational correctness of another SQL Server database, here in a hospital of all places, lives could be at risk!!"
The guard flipped through his list of on-site visitors. "Wait a minute…Phil Factor. He's our Santa Claus! Lovely guy…been visiting us for 10 years! He's due to start handing out presents up there." The guard went misty-eyed, indicating the stairs on the right.
"Thank you officer, that's all the information we need!"
"But then how will the children get their presents? Wait!" he cried, but Celko and Chris were already disappearing up the stairs.
Scene 8: Christmas Blues
The mood in Sander's office was bleak.
"Auditing!" shouted Phil, suddenly animated. "A hospital database must have some pretty watertight auditing, so in theory we can restore the previous good full backup then use the audit log to discover and rerun all the jobs since that backup."
"Possible in theory, yes," sighed Sander. "Possible in the time we've got, no. We've already had the CIO in here once, raging and shouting about when this was going to be fixed. Until hospital staff can log in, they can't start today's round of treatments. We're OK till 9.00 but after that, we're getting into issues of dereliction of patient care!"
"So you have an hour to get a large database back on its feet, minus its transaction log, with minimal data loss, and without any backups," Phil stated, flatly. "So what are our options?"
"I did have one idea," cried Sander. "It sounds crazy, but these are desperate times. We put the database in
EMERGENCY mode then
SINGLE_USER mode and detach it. We create a dummy database with the exact same file structure as our main database, then we stop the SQL Server service, delete the MDF files of the dummy database, copy in the MDF and NDF files of the original database, and restart SQL Server!"
Grant shook his head vehemently "Won't work. You can't trick SQL Server that way. The log file and data files are intricately linked and SQL Server logs everything. All you'll likely succeed in doing this is sending the database into
Sander sighed. Steve and Grant exchanged what can best be described as the 'DBA look of dread'.
"So what are we looking at here? Please don't tell me
DBCC CHECKDB...REPAIR_ALLOW_DATA_LOSS?" cried Grant, as if in real physical pain.
Grant's anguish and the mention of data loss seemed to break something in Ken, who moaned pitifully and crushed his face against the office window. "This day cannot possibly get any worse! Actually, I lie. Here comes the CIO to check on progress."
The CIO burst in, meaning business. He stared around the room in open-mouthed astonishment for a second or two, before regaining his composure.
"Listen, boss, I know this all seems a little weird…but…" blurted Sander
"Weird? No, not at all! Guy with baseball bat, check…guy in bizarre Hawaiian shirt, check…Santy Claus, check. Everything seems to be in order here. Silly me for worrying unduly."
"Ho! Ho! Ho!" boomed Phil jovially, before looking sheepish.
"I promise you, I have the situation under control," asserted Sander, glaring at Phil, and displaying a calmness he didn't feel inside. "This is Phil Factor, who does Santa every year for us. He's brought along some of the DBA Team in cunning disguises, due to a certain difference of opinion with the Federal RPA. They are here to help if we need it. The database will be back up by 9.00."
The CIO shook his head and threw up his arms in a gesture that said 'you're all mad, but what choice do I have'. "OK, Sander, let's see what you can do. I'll be attempting to log in to the database at 9.00 sharp. If it fails, then the Season of Goodwill towards all Men will make a sudden exception, and I'll be back in here around 9.01 for a nice, cozy chat about your future."
With that the CIO stormed out.
Steve sighed "If this fix is heading the way I think, things could get messy. We're going to want to take a copy of the existing data files."
"We've already contacted our errant SAN admin about urgently finding 1TB of SAN space to make copies," said Ken
Grant perked up a little, "Maybe we should, you know, go see if we can persuade him to find that space a little faster…"
Phil picked up his sack, "I hate to leave you guys but I've got several wards worth of toys to distribute. The junior doctors are supplying a couple of elves to help out."
"Do it!" shouted Sander, decisive "All three of you! I need a bit of peace and quiet in here to get into my 'DBA Zone', and see if I can't think of another way out of this sorry mess!"
Scene 9: The Santa Center
The junior doctors were ready. Phil's lonely penance had, over ten years, made its mark in the hospital and it was celebration time. The junior doctors were all dressed up as either Santas or elves. The Staff Room was decked with holly. There were glasses, streamers, and bottles everywhere.
"Quick, everyone!" cried the Anesthetist peering through the door, "he's coming!"
As Phil approached, there was a yell of 'Surprise!' and Phil was unceremoniously dragged into the room.
"This is a big thank-you from the staff of the hospital for ten years of being Santa!" shouted a Pediatrician. A glass was thrust into Phil's hand. There were cries of "Speech! Speech!"
"Firstly, I'd like to thanks the Elf Care system. Without them, none of this would be possible…." Phil paused for a moment to allow for the laughter and a round of wild applause. Then, out of the corner of his eye, he noticed two sinister figures in black suits and shades enter the room.
Silence descended. Celko and Chris paused at the door starting in bewilderment at the scene before their eyes: not one, but 23 identical looking Santas, plus assorted elves. Chris whispered at Celko, bewildered. "I thought Fabian said this would be easy! Which one is Phil?"
Phil, having blended quietly into the throng of Santas, was the first to speak. "Hey, great disguise guys! Federal goons! Imaginative! Join the party!"
There was a resounding cheer from the other 22 Santas, and glasses were thrust into their hands. In a flash, the two were surrounded by slightly-inebriated Santas and Elves.
Phil slipped quietly out the back door.
Scene 10: Iron underpants for Christmas
Steve and Grant burst back into Sander's office.
"Right," shouted Steve, "the SAN admin has found the extra space we need and copied the existing data files over to a separate area."
"Good," cried Phil, who had bustled in right behind them, "because the RPA are in the building! Celko and Chris are currently being…entertained…by some Santas and Elves, but it might not hold them for long!"
"OK, then," said Sander, a glint of steel in his eye. "I really don't want to do this, but it's iron underpants time. I'll put the database in
EMERGENCY mode then
SINGLE_USER mode and run the
DBCC CHECKDB command with
REPAIR_ALLOW_DATA_LOSS. It's bad, I know, but…"
"Bad!? It's terrible!" cried Grant. "First, we have the likely data loss, and if that alone isn't bad enough, SQL Server won't be able to run recovery on restart because the original log was trashed!"
"He's right," sighed Steve. "It means that committed transactions that weren't part of a
CHECKPOINT before the database went down will be missing and, even worse, the database could be in a wildly inconsistent state, because it will have the partial effects of any still-active transactions that were part of a
CHECKPOINT when the disaster occurred."
"I know!" cried Sander "BUT…I wasn't idle while you were all away. I had a good look at our audit logs to see exactly what activity was going on in the database around the time of this accident."
"And?" asked Grant, his voice betraying the faintest glimmer of hope.
"It looks like a job ran and completed shortly before, but that there was no activity at all on the database, in the minutes leading up to the loss of the LUN holding transaction log!"
"So, if there were no active transactions, and a
CHECKPOINT ran before the database went down…" said Steve
Grant whistled. "Well, that might turn out to be the biggest slice of Christmas luck you ever had."
"Hold up," said Phil "if all of that is true, why not just create a new database based on the existing data files and use
ATTACH_REBUILD_LOG to create a new log?"
Steve shook his head this time. "Won't work. It will only ever work if there is a completely clean database shut down, and if there was, SQL Server records it in the database boot page. In this case, there wasn't, so it will need to run recovery…which we know it can't do"
"OK, then, let's do it." Nervous and uncomfortable, Sander started the
DBCC CHECKDB command with an hour to spare before the 9AM deadline. They were the longest minutes of their lives, the tension ratcheting to breaking point, till finally released by a message popping up on the screen indicating success.
Relief flooded the room. Quick as a flash, Sander brought the database back online and ran a quick query to check for the data that was loaded as part of the last job that ran before the database went down.
"I can't believe it! It's there…and it all looks….fine!" It was 8:59. Sander set the database back into multi-user mode.
Steve gave Sander a well-meant but hearty slap on the back that almost flattened him to his desk. "Looks like you got real lucky this time pardner, but you're not out of the woods yet. You need to get some employees of the hospital to start checking the available data and make sure they also find no errors."
"Hey, look what just popped up in the audit log," exclaimed Phil. "Your CIO logged in successfully at 9.00 exactly!"
Scene 11: Elf and Safety
Phil snuck cautiously back into the hospital staff room. He'd persuaded Steve and Grant to go, reluctant for them to end up spending Christmas having a cozy chat with the RPA. But he, Phil Factor, still had some presents to give out.
The elves, all in fetching green tights, sat around Celko. "Joe, tell us once again how you vanquished the heresy of using Hungarian-notation in SQL object naming conventions. …Oh, I see your glass needs topping up," said a helpful Elf, also handing him another minced pie.
"Well, you know, it washn't easy. Hic!" Celko muttered looking up at the clock on the wall. It was strangely indistinct. "Wait, what's the time? Got to arrest Santa!" He staggered to his feet, but slipped on the polished floor. His glass went flying and he crashed back down.
There were gasps from the elves-cum-junior doctors as they surrounded Celko, arms flailing, and choking on the remains of his minced pie.
"Oooh good, a real adult patient at last: I've been dying to try out the Heimlich maneuver!"
"After that, we'll have to examine him for broken bones." cried another.
"We might as well give him a complete checkup. I reckon he'll need a stomach pump. Right, wheel in the box that goes ping, take his clothes off!"
Chris looked on helplessly. At the other end of the room, Phil surveyed the scene with quiet amusement, before gathering together a few of the less inebriated elves nearby.
The DBA Team had once more escaped the clutches of the Relational Protection Agency. Quietly he picked up his sack of toys to once again perform the annual ritual, but this time with a strange, deep contentment infusing his soul.
Scene 12: It's a gift wrap
The next day all five of them, Sander, Ken, Steve, Grant, and Phil, now in normal civilian clothes, strolled the hospital grounds mulling over the previous day's events, and even managing the occasional laugh.
"Man that was intense for a while," Grant chuckled. "Heck, though, it sure beats sitting cross legged choking on the scent of jasmine. I always did hate that stuff!
“WHOAH!!!!" cried Steve suddenly as they rounded the corner. They all jumped back, confronted by a ghostly white figure in Stetson hat, shades and sporting a golden RPA badge.
"Was this your doing, Phil?!" asked Grant, smiling and lifting the hat from the Celko-lookalike snowman and placing it on Steve's head.
Phil smiled, "He left them behind after his examination yesterday!"
"Yeah, it sounds like Celko and Chris got their come-uppance once again, so it wasn't all bad…." said Steve
Phil was thoughtful. "Well, you know, they're not such bad guys. I suppose they weren't to know we never actually committed those crimes against Relational Theory. They're just doing their job, and they do some good, in their own zealous way."
"Yes, I like that!" shouted Sander "The Christmas spirit of good will and forgiveness! We all learned hard lessons here, and me more than anyone. I learned that when the DBA spirits are aligned against us, sometimes we have to do things we really, really don't want to do, in order to get out of a tight spot…and then we get to make sure we NEVER have to do them again!"
"Too right" smiled Grant "And whatever else you do, you better make sure you ALWAYS verify your database backups from now on …or else….I'll be back!!"
"Wrong movie, Grant!" cried Steve "This is the finale of the whole series…don't mess it up!"
"Oops, Sorry. OK….verify your database backups, you crazy ass fools!"
Do you want to always know what's going on with your SQL Server?
SQL Monitor offers performance monitoring and alerting through a web-based UI, so you can keep track of SQL Server wherever you are.
It shows you server performance in real time, so you can spot problems before anyone else notices. And you have the data you need to fix the problem right there in the tool.
SQL Monitor is simple to install, and gives you useful information on Day One. Try it now and see for yourself: