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.

I do the following to get rid of the August data:


/*************************************************
1) Get row count of Sept data
*************************************************/
SELECT COUNT(*) FROM TableName_Staging WHERE CreatedTime >= '9/1/2009' -- 2857208

/*************************************************
2) Copy Sept Data into a Temp Table
-- Confirm row count
*************************************************/
SELECT * INTO #Table_Staging FROM TableName_Staging WHERE CreatedTime >= '9/1/2009'

/*************************************************
3) Truncate Staging Table
4) Copy data from temp back into staging
-- Confirm row count before issuing Commit
*************************************************/
BEGIN TRAN
TRUNCATE TABLE TableName_Staging
INSERT INTO dbo.TableName_Staging
( ColumA ,
ColumnB,
ColumnC ,
ColumnD
)
SELECT * FROM #TableName_Staging

--ROLLBACK
--COMMIT

/*************************************************
2) Drop Temp table
*************************************************/
DROP TABLE #TableName_Staging


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:

USE ALTER TABLE TableName_Staging SWITCH PARTITION 2 TO TableName PARTITION 2

And Bobs your uncle, as a co-worker likes to say.