Friday, January 23, 2009

Partition Management....(Don't forget the Management)

You'd think by now I'd know better than to cut corners. But for some reason, I still somehow seem to decide once in a while for short term gains over long term benefits...

A while back I was overloaded with stuff so I set up a year's worth of new monthly partitions in one of our reporting databases. I did not, however, chose to create any nice job to automatically create new partitions or anything like that, as I did not have time (or so I thought).

Well, 'A while back' is now 18 months ago.... Hmm... 6 months of data in one partition. Ouch.

Might not be a big deal with Oracle, but it's SQLServer 2005. Sadly, just splitting 1 day of data into a new partition takes 10 minutes and 9 GB of transaction log space. Very depressing. (At least I tested and timed it before I tried to split one month's data into a new partition).

Kills me some times. Don't put off till tomorrow what you can do today, if tomorrow might become next year... ;)

Hopefully I'll come up with a slick way to split those partitions faster... we'll see.

And yes, I'm going to create a job to add partitions in advance.... :)