The Multifunctioning DBA

Aug 21 2012   9:10PM GMT

Partition Table Load Mistake



Posted by: Colin Smith
Tags:
SQL Admin
SQL Administration
SQL Server

I have a table that has 54 partitions and is a range right partition function. So ideally I like to have 52 partitions with data in them and the first and the last partitions are empty. Well the other day, our ETL team ran a load job that is meant to run after our weekly load process. They had not run the weekly load process for three weeks prior to running this other job though. This means that I do not have partitions created for dates that they are going to be inserting data for. I looked at my table and I saw that my last end partition, that I like to have 0 rows, had about 15 million rows in it.  I contacted them and they told me that since they had not run the weekly job for so long they thought they would just catch up by loading all of it at the same time. Well this is an issue, becuase now if I split my partition on the weekly date that I need to, SQL Server will have to re-sort all that data into the correct partitions. This is not a quick operation. So I had to do some clean up.

This is fairly easy to do since I did not split my partition yet.

1. Create a stage out table with the same structure, indexes included, as the base table and make sure it is on the same filegroup.

2. Swithc that last partition out to the stage table that you just built.

3. Drop the stage table.

Now all that data is gone and my partitions looked good again. Now I asked the ETL team to run our standard weekly process to catch up and they did. Now all is well with my table.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: