Automate new partition creation in table partitioning

Question

Post reply

Automate new partition creation in table partitioning

SQListic

SSCertifiable

Points: 6904
More actions
May 8, 2013 at 2:58 pm

#277506

Hi All,
we planning to create partitioning on existing tables. The partitioning is on date column, there should be one partition for each year. Creating of new partitions should be automated, and also we dont have any plans of archiving old data, all we want is that new partition creation should be automated
Thanks all

Viewing 7 posts - 1 through 6 (of 6 total)

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi

Here are some links:

https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/

http://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx

http://technet.microsoft.com/en-us/sqlserver/gg545008.aspx

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

SQListic SSCertifiable Points: 6904 More actions · Answer 2

Thanks for the reply IgorMi

I came across these links, as per my understanding sliding window does archiving or dropping of the old partition, but i dont want to implement that. I just want to implement automated partitioning for every year

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Create a job that runs every year and splits a new partition. It's the same as the sliding window except you're only doing the split partition half, not the switch and merge of old data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQListic SSCertifiable Points: 6904 More actions · Answer 4

Thanks Gail,

If you dont mind can you please provide me sample script that should be ran every year. so that i can compare the differences and understand it. sorry for asking, i just want to understand it clearly.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

There's a script in the first article that Igor linked, just take the part to create a new partition and ignore the part to switch and merge the partitions since you only want to add new partitions. (the script is commented)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Craig Smith SSChasing Mays Points: 600 More actions · Answer 6

Here's what I do:

ALTER PARTITION FUNCTION <function_name>()

SPLIT RANGE (@NewDate)

ALTER PARTITION SCHEME <schema_name>

NEXT USED [PRIMARY]

Prior to that I verify that the partition doesn't already exist, but you may not need to bother.