What is your biggest SQL Server mistake or embarrassing incident?

Question

You know the one I am talking about.

We have all been there at some point. You get that awful feeling of dread and the realisation of oh my god did that actually just happen.

Sure you can laugh about it now though, right, so go on and share your SQL Server mishaps with us.

Even better if you can detail how you resolved your issue so that we can learn from our mistakes together.

So in order to get the ball rolling, I will go first……..

It was back in my early years as a junior SQL Server Guru. I was racing around Enterprise Manager, performing a few admin duties. You know how it is, checking a few logs, ensuring the backups ran ok, a little database housekeeping, pretty much going about business on autopilot and hitting the enter key on the usual prompts that pop up.

Oh wait, was that a “Are you sure you wish to delete this table” prompt. Too late!

Just to confirm for any aspiring DBA’s out there, deleting a production table is a very very bad thing!

Needless to say a world record was promptly set for the fastest database restore to a new database, swiftly followed by a table migration, oh yeah. Everyone else was none the wiser of course but still a valuable lesson learnt. Concentrate!

Answer 1

I suppose everyone has missed the WHERE clause off a DELETE or UPDATE at some point...

Answer 2

I've seen plenty other people miss a WHERE clause.

Myself, I always type the WHERE clause first, and then go back to the start of the line and type in the rest of the query :)

Answer 3

Columns are nullable, and parameter values fail to retrieve the correct information...

Answer 4

Inserted 5 million test persons into a production database. The biggest mistake in my opinion was to let me have write access to the production db in the first place. :P Bad dba!

Answer 5

My biggest SQL Server mistake was assuming it was as capable as Oracle when it came to concurrency.

Let me explain.

When it comes to transactional isolation level in SQL Server you have two choices:

1. Dirty reads: transactions can see uncommitted data (from other transactions); or
2. Selects block on uncommitted updates.

I believe these come from ANSI SQL.

(2) is the default isolation level and (imho) the lesser of two evils. But it's a huge problem for any long-running process. I had to do a batch load of data and could only do it out of hours because it killed the website while it was running (it took 10-20 minutes as it was inserting half a million records).

Oracle on the other hand has MVCC. This basically means every transaction will see a consistent view of the data. They won't see uncommitted data (unless you set the isolation level to do that). Nor do they block on uncommitted transactions (I was stunned at the idea an allegedly enterprise database would consider this acceptable on a concurrency basis).

Suffice it to say, it was a learning experience.

And ya knkow what? Even MySQL has MVCC.

Answer 6

I changed all of the prices to zero on a high-volume, production, eCommerce site. I had to take the site down and restore the DB from backup.. VERY UGLY.

Luckily, that was a LOOONG time ago.

Answer 7

Thankfully we only ever make one cock-up before you realise that using transactions really is very, very trivial. I've amended thousands of records on accident before, luckily roll-back is there...

If you're querying the live environment without having thoroughly tested your scripts then I think embarrassing should really be foolhardy or perhaps unprofessional.

Answer 8

forgetting to highlight the WHERE clause when updating or deleting

scripting procs and checking drop dependent objects first and running this on production

Answer 9

I worked with a junior developer once who got confused and called "Drop" instead of "Delete" on a table.

It was a long night working to get the backup restored...

Edit: I should have mentioned, this was in a production environment, and the table was full of data...

Answer 10

Like zabzonk said, forgot the WHERE clause on an update or two in my day.

Answer 11

The biggest mistake was giving developers "write" access to the production DB many DEV and TEST records were inserted / overwritten and backup- ed too production until it was wisely suggested (by me!) to only allow read access!

Answer 12

1. Take a big database (terabyte+) with over 1500 tables, almost all of which are dependant on other tables.
2. Run a DTS package to copy some new schema objects in as part of an upgrade / data consolidation.
3. Leave the "Include all dependent objects" and "Drop destination objects first" ticked.
4. Boom.
5. Cry.

Answer 13

Starting off a restore from last week onto a production instance instead of the development instance. Not a good morning.

Answer 14

Sort of SQL-server related. I remember learning about how important it is to always dispose of a SqlDataReader. I had a system that worked fine in development, and happened to be running against the ERP database. In production, it brought down the database because I assumed it was enough to close SqlConnection, and had hundreds, if not thousands of open connections.

Answer 15

We had an old application that didn't handle syncing with our HR database for name updates very efficiently, mainly due to the way they keyed in changes to titles. Anyway, a certain woman got married, and I had to write a database change request to update her last name, I forgot the where clause and everyone in said application's name was now Allison Smith.

Answer 16

I was working on the payment system on a large online business. Multi-million Euro business.

• Get a script from a colleague with a few small updates.
• Run it on production.
• Get an error report 30 minutes later from helpdesk, complaining about no purchases last 30 minutes.
• Discover that all connections are waiting on a table lock to be released
• Discover that the script from my colleague started with an explicit BEGIN TRANSACTION and expected me to manually type COMMIT TRANSACTION at the end.
• Explain to boss why 30 minutes of sales were lost.
• Blame myself for not reading the script documentation properly.

Answer 17

At the start of my co-op term I ended up expiring access to everyone who used this particular system (which was used by a lot of applications in my Province). In my defense, I was new to SQL Server Management Studio and didn't know that you could 'open' tables and edit specific entries with a sql statement.

I expired all the user access with a simple UPDATE statement (access to this application was given by a user account on the SQL box as well as a specific entry in an access table) but when I went to highlight that very statement and run it, I didn't include the WHERE clause.

A common mistake I'm told. The quick fix was unexpire everyones accounts (including accounts that were supposed to be expired) until the database could be backed up. Now I either open tables and select specific entries with SQL or I wrap absolutely everything inside a transaction followed by an immediate rollback.

Answer 18

One of my favorites happened in an automated import when the client changed the data structure without telling us first. The Social Security number column and the amount of money we were to pay the person got switched. Luckily we found it before the system tried to pay someone his social security number. We now have checks in automated imports that look for funny data before running and stop it if the data seems odd.

Answer 19

Our IT Ops decided to upgrade to SQL 2005 from SQL 2000.

The next Monday, users were asking why their app didn't work. Errors like:

DTS Not found etc.

This lead to a nice set of 3 Saturdays in the office rebuilding the packages in SSIS with a good overtime package :)

Answer 20

Not, exactly a "mistake" but back when I was first learning PHP and MYSQL I would spend hours daily, trying to figure out why my code was not working, not knowing that I had the wrong password/username/host/database credentials to my SQL database. You cant believe how much time I wasted on that, and to make it even worse this was not a one time incident. But LOL, its all good, it builds character.

Answer 21

I once, and only once, typed something similar to the following:

psql> UPDATE big_table SET foo=0; WHERE bar=123

I managed to fix the error rather quickly. Since that and another error my updates always start out as:

psql> UPDATE table SET WHERE foo='bar';

Much easier to avoid errors that way.

Answer 22

This was before the days when Google could help. I didn't encounter this problem with SQL Server, but with it's ugly older cousin Sybase.

I updated a table schema in a production environment. Not understanding at the time that stored procedures that use SELECT * must be recompiled to pickup new fields, I proceeded to spend the next eight hours trying to figure out why the stored procedure that performed a key piece of work kept failing. Only after a server reboot did I clue in.

Losing thousands of dollars and hundreds of (end user) man-hours at your flagship customer's site is quite an educational experience. Highly recommended!!

Answer 23

A healthy amount of years ago I was working on a clients site, that had a nice script to clear the dev environment of all orders, carts and customers.. to ease up testing, so I of course put the damn script on the productions server query analyzer and ran it.

Took some 5-6 minutes to run too, I was bitching about how slow the dev server was until the number of deleted rows came up. :)

Fortunately I had just ran a full backup since I was about to do an installation..

Answer 24

Beyond the typical where clause error. Ran a drop on an incorrect DB and thus had to run a restore. Now I triple check my server name. Thankfully I had a good backup.

Answer 25

I set the maximum server memory to 0. I was thinking at the time that would automatically tell SQL server to use all available memory (It was early). No such luck. SQL server decided to use only 16 MB and I had to connect in single user mode to get the setting changed back.

Answer 26

Hit "Restore" instead of "Backup" in Management Studio.