Adding new partition to filegroup

Question

Adding new partition to filegroup

mqbk

SSCommitted

Points: 1513
More actions
November 28, 2014 at 5:52 am

#313413

I work for a 24/7 shop. We currently have a table that is partition on monthly. I have to created a script that will add a new file group, add the new file to the group, and alter the the partition scheme and function. However, I need for this process to not cause a lock on the table. Typically I get the locking and issues when I am run the split command. Is there a way to prevent this from happening?

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

You will always get a lock on the table. It should be short-lived. Make sure that your split isn't forcing data to move from one partition to another, as that will make the split potentially run long.

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

mqbk SSCommitted Points: 1513 More actions · Answer 2

GilaMonster (11/28/2014)
You will always get a lock on the table. It should be short-lived. Make sure that your split isn't forcing data to move from one partition to another, as that will make the split potentially run long.

so if I run the script today to create next months partition, the lock should be short lived because we don't have any records for December as of yet. Is this a correct statement?

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

Well, without seeing the script, the table and being able to query the data, I can't say for sure, but ideally yes.

Before you do so, make sure that there is no data that would fall on the 'other side' of the new partition boundary you're creating. You also want to run this at the time the table is quietest. I know it's probably never idle, but lowest query volume.

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

mqbk SSCommitted Points: 1513 More actions · Answer 4

Thank you that answered my question. I know there isn't any data on that side of the partition because the date field I am using uses getdate() as the default. We haven't made it to December yet.