So it happened....
I screwed up....
Here's the setup...Wednesday morning, a user deletes about 1600 rows out of a table (why they have that type of access is beyond me and probably will be good for another blog entry). NO PROBLEM! Let me log into the server, and locate my backup from last night. Click, double click, another double click, scroll...scroll...scroll some more?...panic sets in. Where in the H-E-Double Hockey sticks is my backup from last night (It came out a lot worse than that), where is my backup from Tuesday night, Monday, SUNDAY!
As you can guess my Rookie badge was gleaming at this point....
What had happened was (keep in mind SQL vets...Rookie here) my drive ran out of disk space, so the lovely Maintenance Plan (Geez I hate Maintenance Plans) simply stopped working, I guess it really had no other choice. And of course their is an alert (setup by our AD guys) that sends me an email letting me know when a drive is low on space, but I'm not alerted until the drive falls below 100mb, does me a lot of good on a backup drive. The big kicker was I have alerts sent out every night letting me know the status of my Maintenance Plans, and I religiously check them (honestly I do). But this time, no not this time, Not starting on Monday, I didn't or Tuesday, and obviously I didn't on Wednesday. The name is DUMASS!
So lets get back to the issue...
So I am telling the applications admin the bad news...and then BAM! WAIT! Hey, app guy, do we know which table was deleted? Can we find out? And while you're at it, get an estimate when the table was last updated. Turns out they did know which table was "edited" and no changes have been made in about a week! (Prior to Wednesday of course) And it turns out Brent (thats me) has a freaking wonderful tool from RedGate called SQL Data Compare. So I simply compared my Saturday night backup with the current state of the database, saw the table that was nulled out, all 1600 rows, and synchronized only that table with the current database. And like freaking magic, the table had been restored.
Now obviously this was a hail mary, almost exactly like Flutie's Miracle in Miami, "almost" exactly. This was without a doubt a lessons learned, in my young SQL career.
So what did I do to assure this will not happen again, and avoid being fired in the future?
1. Went straight to my AD guy and had him adjust the alert, so alerting occurs in the Gigs, and not the Mbs.
2. I tuned up SQL Monitor, so I get up to the minute reporting.
3. Then I said screw the local disk for storing backups for seven days, I'm going to utilize Data Domain
3a. Write a script that sends the backup off the disk, and up to Data Domain, and make sure the script cleans up after itself, deleting the backup locally.
4. And of course, check my ALERTS!
And there you have it, my horrible Wednesday, but fortunately, I learn from my mistakes, unfortunately, thats how I learn!
If anyone out there has suggestions, please feel free to comment and help a SQL Rook out!
Friday, May 27, 2011
Friday, May 20, 2011
Death to Maintenance Plans!!!
Let me start out by saying, Yes this is probably obvious to seasoned vets, but for rooks like me, its made a world of difference
I've found it hard to find topics to blog about. I recently attended SQLRally in Orlando, and met a lot of great people. Of which I spoke with a few in particular about how to get going with my blog, and was given great advice by Mr. Wes Brown (twitter|blog). "Blog about what you did at work that day," he told me. It sounded so simple and almost obvious, Great advice Wes, thanks, and here we go!
Maintenance Plans, as an "accidental" DBA, maintenance plans (MP) were a pain in my rear and a mess. I stepped into a situation where not all databases were being backed up, let alone having maintenance run on them. Early on I struggled to understand what all went into a MP, until I found this e-book (free, BTW) by Brad McGehee. This book although simple, and very entry level, was perfect for me and for someone just getting into SQL Server. After reading this book from the 1st page to the last, I finally felt as if I had a good understanding of MPs.
Enter Ola Hallengren, this site blew me away! I ran across this site a while back, but at the time (before I read Brad McGehee's book) it did me little good. But now I was armed with the knowledge of MP's, now I want to make them better!
Here's what I did, instead of using the MP wizard, etc, and having to waste time creating new MPs everytime a new database came online, I implemented Ola's scripts going from about 50 individual MPs per Server (I know small potatoes) to one SQL Job with three steps...AMAZING!
(You must run the Store Procedure on Ola's site prior to running these scripts, 'MaintenanceSolution.sql')
Step 1: Database Integrity Check- Basic integrity check
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES'
Step 2: Database Index Optimize- This script is pretty slick, because instead of rebuilding the entire db like we had to do with MP, this script allows us to get more granular. Based on the level of fragmentation will determine how this script reacts.
EXECUTE dbo.IndexOptimize @Databases ='USER_DATABASES',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationLow = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
Step 3: User Database Backup- This script allows us to create Full backups with Verify, and CheckSum in addition cleans up after itself (just like a good script should).
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'G:\Backups\',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@CleanupTime = 168
So in three simple scripts I was able to go from having way too many MPs to manage and a hot mess, to a very organized and precise Job, that accomplishes what I want, the way I want it to. Within the job, we still setup notifications upon failure, and scheduling. So if you are Accidental to SQL like me, and hate working with Maintenance Plans try this out, it will make your life a whole lot easier.
I've found it hard to find topics to blog about. I recently attended SQLRally in Orlando, and met a lot of great people. Of which I spoke with a few in particular about how to get going with my blog, and was given great advice by Mr. Wes Brown (twitter|blog). "Blog about what you did at work that day," he told me. It sounded so simple and almost obvious, Great advice Wes, thanks, and here we go!
Maintenance Plans, as an "accidental" DBA, maintenance plans (MP) were a pain in my rear and a mess. I stepped into a situation where not all databases were being backed up, let alone having maintenance run on them. Early on I struggled to understand what all went into a MP, until I found this e-book (free, BTW) by Brad McGehee. This book although simple, and very entry level, was perfect for me and for someone just getting into SQL Server. After reading this book from the 1st page to the last, I finally felt as if I had a good understanding of MPs.
Enter Ola Hallengren, this site blew me away! I ran across this site a while back, but at the time (before I read Brad McGehee's book) it did me little good. But now I was armed with the knowledge of MP's, now I want to make them better!
Here's what I did, instead of using the MP wizard, etc, and having to waste time creating new MPs everytime a new database came online, I implemented Ola's scripts going from about 50 individual MPs per Server (I know small potatoes) to one SQL Job with three steps...AMAZING!
(You must run the Store Procedure on Ola's site prior to running these scripts, 'MaintenanceSolution.sql')
Step 1: Database Integrity Check- Basic integrity check
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES'
Step 2: Database Index Optimize- This script is pretty slick, because instead of rebuilding the entire db like we had to do with MP, this script allows us to get more granular. Based on the level of fragmentation will determine how this script reacts.
EXECUTE dbo.IndexOptimize @Databases ='USER_DATABASES',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationLow = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
Step 3: User Database Backup- This script allows us to create Full backups with Verify, and CheckSum in addition cleans up after itself (just like a good script should).
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'G:\Backups\',
@BackupType = 'FULL',
@Verify = 'Y',
@CheckSum = 'Y',
@CleanupTime = 168
So in three simple scripts I was able to go from having way too many MPs to manage and a hot mess, to a very organized and precise Job, that accomplishes what I want, the way I want it to. Within the job, we still setup notifications upon failure, and scheduling. So if you are Accidental to SQL like me, and hate working with Maintenance Plans try this out, it will make your life a whole lot easier.
Subscribe to:
Posts (Atom)