This is a blog about stuff I come across as a DBA; fun stuff, hard stuff, weird stuff...just the random day to day stuff that I encounter.

Wednesday, October 7, 2009

Fixing a partition problem

So last time I wrote about using a partition and mentioned it can be used for archiving data; I came across an issue where because of an error Septembers partition didn't start on the first of the month, like it should, so Augusts partition contains data for September. This is a problem because we want to archive off the data for August, but if we stop and drop the August partition then we will also get rid of the few days of September as well and we don't want to do that.

We have a few options for resolving this, most aren't good.1) we can re-configure the partition.-- This is bad because of blocking and because of the log file size

2) We can keep the August partition and delete the august data.-- This is bad, again because of the file size.

3) We can keep the August partition and find a different way of getting rid of the data.-- This has promise.

What I'm going to end up doing is this:

First I am going to do my normal BCP out of the August data.

Next, make an exact schema copy of the tables in question.

Next switch the August partition to the new tables using this code for each table:USE DatabaseName ALTER TABLE TableName SWITCH PARTITION 2 TO TableName_Staging PARTITION 2

Now that the data is in a "_Staging" table I can work with it without causing blocking on the main table.

So now I should have removed the August data from the _Staging table and rater than deleting 50 million rows, I only had to copy 2.8 million rows; which is a much smaller hit on my tran log file and much, much faster.

Now I switch the partition back from the staging table to the live table:

No comments:

Post a Comment

About Me

Hello, I'm a database architect with a background in software engineering and website design. I have been working with Databases since 2000, and specifically as a DBA since 2005. In my off time, I enjoy geocaching (high tech treasure hunting), and Scouts.