I was curious to know what has been your biggest fail and you biggest success when working with SQL Server ? My biggest fail has to have been when I scripted a process for changing all db objects schema to another schema and then not having a script to switch them back. (IN PORDUCTION) :S. I sweated a lot but no one noticed at the time. Strange but I can not rememeber why... Maybe was OOH.... it was some time ago (8 - 9 years) but had recently been on a training course Introduction to SQL Server 2000 by a well know training company. So my biggest success has to be not getting fired and rectify the problem before anyone noticed :D I will select the best answer based on votes. Democracy and all that :D
Simple, taking over support of a server an not checking for all the normal stuff. Things like backups. Then one day getting a call that the server is toast and not having a recent backup to restore. That was the Biggest Fail. The Biggest Success was keeping that job after that incident. My boss said he never had to worry about me not having backups again. :)
My biggest fail was writing an update statement and when I ran it didn't use a begin tran and forgot to include the where statement, fortunately using SQL Backup I managed to restore the data without too many people finding out.
I am lucky enough to not really have had a 'biggest fail' personally - but I can easily recount a time when the team I was in failed spectacularly. That was when a disk failed in a shelf, and hp's website required you to enter the serial number of the chassis when ordering a replacement disk. The ops guys at the time didn't know the serial number of the chassis, and therefore decided to pull the chassis out of the rack it was in. About 15 seconds later I rushed in saying 'database XXX is marked suspect - WTF'. Ok let's restore from backups. Oh. Looks like they haven't been working for a while. Cue me spending a 70 hour week restoring a 750Gb database the hard way. I think my biggest success has to be my Atlantis stuff. Not because I've sold loads of tools or made any money - but because taking the time to write those tools caused me to learn more about SQL Server than I had done in my entire career up to that point...
My biggest fail was on SQL Server 7 about 10-11 years ago. We were doing an upgrade to an application and had a twelve hour window. It was going slow so about 8 hours in I tried killing some SPIDs to free up resources. Of course I killed the wrong one and we then had to wait for 8 hours as the rollback happened. The team was not impressed. My biggest success? Technically I guess it was the set-up and tuning of a multi-terabyte system that performed lottery forensics and resulted in the arrest of some fraudsters. Otherwise I would have to say it was falling into supporting SQL Server and realizing that I had a passion for it.
My biggest failure was on SQL Server 7 about 10 years ago. I was working on the tax calculation engine rule for a small telecom. The tax rules in this industry are extremely complicated (not that they are simple in any industry). For instance, there are over 1700 local tax jurisdictions in Texas alone and the long distance phone calls are subjected to taxing by some of these depending on the origination location of the call. Additionally, there are common state and federal taxes for all areas and industry-specific charges based on the provider / area, such as the urban guy may pay addition charge which is then collected and distributed among the providers who agree to provide services in rural areas. My task was to use the available information and deduce the exact rules for all areas / providers, so it included writing a lot of ad-hoc fuzzy logic queries and storing data. When I was almost done, I needed to clean up, which included deleting about 10 million records from one table. I was not aware of the fact that the dev server which was given to me had a failing hardware. All I knew was that our main prod server was the state of the art machine (in that time) with 4 whopping 286 Mhz Pentium II processors and 200 GB hard drive, so I assumed that the dev server is similar. Right before I issued the delete statement which I assumed will be completed before I come back to work next day, our DBA saw it and told me "It will crap out, Oleg, don't do it". Unfortunately, I did not listen to his advice and started the query anyway just to be greeted with "it has been marked as suspect by recovery" message. Since I had no clue about what it meant, I requested a restore from yesterday's dump but was told that that dev server was not subjected to any backups. It was a dying box soon to be replaced. To make the long story short, I lost a lot of time and had to redo most part of that nasty, tedious work. I had learned a great deal from watching our DBA trying to revive the database by writing a bunch of low level dbcc commands, and it made me clearly realize how much more I have to learn before I can even dream about comfortably working with SQL Server. My biggest success was about a year ago when I was able to make the custom merge replication process at the client site about 50 times faster by switching from RBAR processing to set-based merge of the data with deltas serialized as XML. Oleg
My biggest oops would be between issuing a begin tran and getting distracted and not committing or rolling back the change until after users started calling the help desk that the application was down OR between doing a quick update statement on a table with 100M rows and forgetting my where clause. Fortunately that was in the wee hours of the morning after a full backup had been ran. WHEW! My biggest success would probably be completing a data center move. My team was listed as one of the worst teams on the data center migration project. We had some management change up and I was made lead on the project. We ended up finishing first by having all of our servers moved out of the data center into the new one. During this migration I was able to build out several new consolidation servers, upgrade over 300 databases from SQL 2000 to 2005/2008 and perform a physical to physical clone of an aging server. All in all over 40 database servers were migrated. Many of which had to be renamed to match a new naming standard in the data center.
My recent failure(not my biggest:-) is a month ago when i tried to migrate from old physical server running sql server 2005 to a new iron running SQL 2008 R2. I took a backup for precaution and went ahead and did inplace upgrade on old Server to make it 2008R2 so that it will be used as a backout plan if things didnt go as expected in the new server. Unfortunately the DVD image used was corrupted and the installation failed in the middle, making the old instance useless. So i had get a new copy of SQL; copy over the backup to the new server and restore it. However, i forgot to prepare scripts to move the logins, SSIS packages, jobs so it took me a while to use system database backups and get the new system up and running(i even had to rebuild few packages too). So the whole process took me more than the planned/scheduled downtime, which leads to working long hours to the night and emails from application owners. Also I have also been a victim of deleting/upgrading the wrong table/object with out double checking twice but always have my backup to depend on. Best success so far is making the move to Database/SQL Server from engineering and start hanging out in the SQL community especially this site.