Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Moving data between partitions

Moving data between partitions

Moving data between partitions

Hi guys,

We have a mission-critical database with a very large audit table; more than 200 million rows and growing as I type. This table is one of the bottlenecks of our system. Most user activity on the front-end is audited and a large number of checks are performed against the audit table. The server (2012 EE) is on Always-On Availability and as such the database is replicated instantly to another server through Active Sync.

I restored a backup to a different database on the server containing the replica of the database with the aim of testing partitioning as part of the solution. The transaction date and time clustered index was partitioned and queries that took 5 minutes to run are now running in 7 seconds. See the partition function below:


CREATE PARTITION FUNCTION TransactionTimePF (datetime)
AS RANGE LEFT FOR VALUES ('2013-12-31', '2014-06-30','2014-09-30')

Now that the test proved mostly successful, I am face with the issue of moving the data between partitions as it ages. Apparently the only way to achieve this is to split, merge or switch partitions. My question is, what is the best approach for moving data between partitions? Is there a way to automate the process or perform the data movement dynamically? I have seen some code online and I have saved a few links but I am not satisfied, between the scant articles and my lack of experience in the matter. I don't want to create a solution that I won't be able to maintain or one that becomes a problem over time.

Thank you for your advice.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA), Training HOTT cert SQL Server 2012 Business Intelligence (SSIS, SSAS).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close