Blog ala Vidar

Many of you probably noticed TeamDoc had some issues today. It was my fault, but before you shoot me, let me explain what happen, so it won’t happen to you! You’ve maybe noticed when doing changes to big tables, management studio goes to timeout, because it takes too long. The work around for me has always been to press the “Generate Change Script”, copy the script to a new query window, and execute it. Since there’s no timeout set on query windows, this will execute till it’s finished. And in ALL cases this has worked, until today.

We found a bug in TeamDoc, which was related to the Folder field in the Documents-table allowing NULL. We were filtering WHERE Folder + ‘ ‘ + Title LIKE ‘%’ + @SearchText + ‘%’. When Folder is NULL, this doesn’t work. So, instead of changing all SPs to handle NULL-values, we decided to change the field in the table, to not allow NULL, but have (‘’) as default value (because we just changed it to allow NULL). Since I’ve changed the design of this table about 1000 times before, I know it will go to timeout, so I automatically pressed the “Generate Change Script” button, copied the script to a new window, and executed it. Guess what happened! YEAH! Errors.

What happens when you change a field like this is that it creates a new table (tmp_your_table_name), puts the records in this table, then it recreates the original table (DROP, CREATE), then it inserts the records back from tmp_your_table_name, and puts on all indexes etc. The boring thing is that when inserting into tmp_your_table_name it’s not that cleaver that it puts a ISNULL around the Folder-field. And what happens? Well, it doesn’t insert any records, then it drops the original table, and creates it again. What about transactions? Well, it creates transactions inside this script, but not AROUND the script, so there’s no “master” transaction. Therefore, the data of the Documents-table was lost. JIPPI!

We quickly ran in to JanCB to restore a backup, to get the records out as soon as possible. We had a backup from about 09:00, and a log from about 12:00. But, before 12:00 I did some tests (setting the initial size of MDF and LDF files), and took some backups to test if it had any influence on the .bak file. Btw, there were no change to the size of the bak-file. Then we remembered that the old server was still running, so we picked the records of the Documents-table from this. Then we’re only missing the TeamDocs created from Sunday night, till now. There were only 3, so we created them manually by inserting based on the inputs table.

If I hadn’t done any tests, including backups, we could have restored the backup from 09:00, then the log from 12:00 and then a transaction log to a point-in-time, right before I did the change, and picked the data from that database. That would have taken a lot more time though…

ANYHOW. Everything is up and running, and there shouldn’t be anything missing. Sorry for making TeamDocs unavailable 🙂

NB! We found one very nice feature in 2008. I was aware of this, but not that it was THAT efficient! Backup compression! Without compression, it takes about 30 minutes to create the 89GB bak file. With compression, it takes 4 minutes, and the bak file isn’t more than 34GB! That’s impressive!