August 22, 2007 at 7:19 am
So I was changing a table yesterday from nvarchar to varchar (and smaller) on a summary of traces run on the server the last 10 months. The table took 200 gb, and the 2 columns I needed an index on was too wide. So I figured if I change it to varchar I should both save a lot of space (697 million records in this table), and allow me to put a good index on it.
So I made the changes in SSMS, but saved the script and ran the script itself. Sometime over night I ran out of log file space. This of course caused the insert into the temporary table to fail, so it rolled that change back, but still did the rename and then dropped the original table......
So now the data is gone! Luckily this was on test, and I had the summaries on the data I really needed, just wanted to investigate the last 2 months worth of traces....
Lesson learned: Always do backups before you make changes to tables (which in my defense I always do on production systems!).
August 22, 2007 at 9:41 am
and always check your code actually does what it should when it should.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply