Ok so I made a mistake last time around, and I actually was tagged by Kendal Van Dyke, though I missed it back in November. So in the spirit of the exercise here are my two biggest blunders in SQL Server based on the Two mistakes post started by Chris Shaw that circled the rounds of SQL bloggers in November.
While upgrading the second largest database in SQL Server at my job from SQL Server 2000 to SQL Server 2005, one that holds important financial's data for credit card reconciliation's, it was decided by the business users that a large portion of the data in the database would be archived off, and then deleted from the database. At the time the database was just under 140GB in size with the majority of the data in one very large table.
The end users worked with the vendor to get a script to handle this operation, and in testing it, the process was going to run over 30 hrs which was not going to be possible. So after some review, and discussion with the vendors support and development staff, I realized that it would be faster to create a new copy of the table, insert the rows to be kept into it, drop the old table and rename the new one, and rebuild all the constraints and indexes. This took the process down to about 1/2 hr in testing.
The problem came when I loaded the script on production to run the purge process. In testing it, I did the operation in an explicit transaction, and initially I had left the SET IDENTITY INSERT statements out of the batch by mistake. It wasn't a problem on development because I had blocked the code in an explicit transaction, so when the inserts failed because I didn't SET IDENTITY_INSERT ON, I issued a rollback, waited, and then fixed the problem. For some reason, I never saved the script.
Onto production, load the script, run it, and watch a 88GB table disappear in mere seconds. Now I know that everyone has made a mistake at some point, and we are all familiar with that sinking hot feeling that you get in the seat of your pants when you realize it. Take the worst case of that feeling you have ever had, and multiply it by a factor of 10.
The good thing was I had backups, and the database was fully logged, so backup the tail log (first thing I did was let out a string of words that can not be repeated and caused the consultants in the cube next to mine to look over the wall and then take a break, but the log backup immediately followed) and then I took a quick walk to my directors office to explain the disaster I had just created, how long it would take to fix, and that I needed to take a quick walk around the parking garage to clear my head before attempting to fix it, which he agreed was probably a good idea.
Lesson Learned: Save early and save often. Really, I was fine, my backups existed and had been tested numerous time recently in performing mock upgrades so I knew I was able to recover from it. The total impact was about 20 additional minutes of downtime over my initial estimate which had provided me ample wiggle room for a major problem requiring a restore of the database to a SQL Server 2000 instance if need be.
Plan for the worst, hope for the best, but never be without a good backup.
Second biggest mistake...
The second worst thing I have ever done in SQL Server involves a table with an audit trigger doing a SELECT * FROM Inserted. This was at my previous job, and just one of a few messes I created along my road to learning SQL. I was initially a Business Analyst, and in order to track problematic accounts in a new billing system I created a table that exempted accounts from billing during normal billing batches. Anything kicked out during post billing QA or any new accounts were immediately inserted into this table. To maintain a history of problems to ensure no account consistently had problems, an audit table was created using a revisioning trigger that tracked when the account was inserted into the table, when it was updated and then later deleted from the table, and it tracked who did it.
While I was away on Army training this table was changed by a new BA who took over the job I was doing and a column was added to the table to hold the account number which existed elsewhere and was easily gotten with a join. This broke every report that used this table because the column was originally distinct and did not use two part naming (mistake one). When I returned my first task assignment was to fix the reports since they were important to the business users and QA department. After looking at them, the problem was immediately apparent, so to fix it I just dropped that new column in production.
Remember above where I said all new accounts were put immediately into this table. Well dropping the column on the table without dropping it on the audit table caused an error in the trigger execution because you can't INSERT INTO AuditTable SELECT * FROM inserted without a column specification if the column lists are different. This caused the transaction that created all new accounts to rollback and raise an error to the Account creation system which was in a different database.
It only took about 10 minutes before I got a phone call asking if I had touched that table by one of our DBA's. Total Damage 14 new accounts that had to be reentered manually in the Account creation system to send them back to the billing system.
Lesson Learned: First this taught the DBA an important lesson. Jon had no business having any kind of privileges in a production database environment, least of all sysadmin like I did back then. Second, never change something in production that has not been completely evaluated and reviewed by your DBA staff. Third, always fully qualify naming of your columns and tables in your code. A simple change can break so much if you don't. Forth and lastly, never do a INSERT INTO without specifying the column list, and never do a SELECT * in code like this. It will break when you least expect it.
Since this was from so long ago, I won't call anyone else out on it, but if you are interested, call yourself out, and post your two biggest mistakes. Leave me a comment, and I'll update to link to your blog, as a self call out.