How to add new partitions automatically to a table when new quarter started.

Question

Post reply

How to add new partitions automatically to a table when new quarter started.

theunique1011

Right there with Babe

Points: 795
More actions
May 3, 2011 at 7:40 am

#237449

I have a table in SQL Server 2008 R2.
It has 3 years data i partitioned it in to 12 partitions(Quarterly). based on datetime column.
In feature as new data inserted (or new quarter started) a new partition should add automatically. and the older partition should be deleted automatically.
As time frame moving on at any point of time i just want 3 yrs of data only. New data should go in to new partition and data older than 3yrs should be deleted automatically.
Can any help me on this?

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Well a job to delete the old partition.

Another job could create the next one.

I've read a couple times taht guys create like a couple extra years of partitions in advance so they don't have to bother with it. I've never tested that option so I can't vouch for it. But it sounds resonable. :unsure:

theunique1011 Right there with Babe Points: 795 More actions · Answer 2

Can you explain me how to create a job to add and delete partitions automatically?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That will take you some work to get to work.

Script the code to delete the current oldest partition. Then modify is with dynamic SQL so that you can account for the current date.

Then create a new job (SQL agent). You can't schedule for quarterly. You'll have to use monthly schedule and then put an if at the begining to see if the month(getdate()) is correct.

read up in books online and comeback if you have any questions.

theunique1011 Right there with Babe Points: 795 More actions · Answer 4

Thank you for reply. i couldn't find in Books online. can you please write code for me to delete quarter data and add new partition automatically.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That seems way over your head... maybe you should hire a consultant for a day to get that done and do a health check of your server.

In books online DROP PARTITION.

You have to dynamically figure out the name of the partition you need to drop. That also means you need to have a naming convention in place.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

P.S. If it's THAT urgent then hire someone to do it for you.

This is advanced stuff and you should not start that project under pressure. And whatever you decide to do, make sure you TEST on a TEST DB, not prod.

Edit OP Started new thread for the same question after only minutes of no answer here.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

Ninja's_RGR'us (5/4/2011)
P.S. If it's THAT urgent then hire someone to do it for you.
This is advanced stuff and you should not start that project under pressure. And whatever you decide to do, make sure you TEST on a TEST DB, not prod.

I second this opinion at least twice !!!!

I could post the full service jobs, but I especially in this case that might cause you more harm than good. You need some prerequisites. One of them is knowing partitioning, not just using it !!!

There is good info on partition switching, ... in bol, at SSC and on the rest of the net.

You need to be comfortable when handling partitions so training is mandatory !

http://www.sqlservercentral.com/Forums/Topic417556-146-1.aspx

http://www.mssqltips.com/tip.asp?tip=1914

http://msdn2.microsoft.com/en-us/library/ms345146.aspx

There is no such thing as to rush tweaking your new ferrari engine if you are only used to handling 2CV problems.

( The above is just a metaphor ! no offence intented !)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 8

I'd also like to suggest that you don't delete the old data. Push it off into an Archive table of the same structure. This will help you if there's any mistake in the repartitioning.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Mike01 SSChampion Points: 11395 More actions · Answer 9

One of the scripts that we use is to create and load data into a work table with the same layout as the partitioned table. Then when the loading is done, we apply indexes and constraints and then swap it in to the partition for the oldest partition

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 10

How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 11

theunique1011 (5/3/2011)
Can you explain me how to create a job to add and delete partitions automatically?

Yes... please see the link that SQL Kiwi provided above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 12

theunique1011 (5/4/2011)
Thank you for reply. i couldn't find in Books online. can you please write code for me to delete quarter data and add new partition automatically.

No... please see the link that SQL Kiwi provided above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

:w00t:

What link is that Jeff?

Looks like you're catching up the backlog of e-mails or something... thread ended over 10 days ago!

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 14

Ninja's_RGR'us (5/15/2011)
:w00t:
What link is that Jeff?
Looks like you're catching up the backlog of e-mails or something... thread ended over 10 days ago!

Heh... Considering this OP has reposted the same question on another thread, I don't care if the thread ended 2 years ago. 😉 The link I spoke of is immediately above the post where I mentioned it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)