I have a database that is used for current operations. The amount of data is starting to get a bit to much for the applications using it.

Most of the data is older and is very rarely needed.

I am wondering if there is a way to configure SQL Server to keep a mirror of the original database, but allow me to occasionally purge out old data (and not have it purge from the copy).

NOTE: My database is currently SQL Server 2008 R2, but we will be upgrading to SQL Server 2012 soon.


Define the data is getting to be a bit much? I've got clients with 1B+ rows in a table and no performance problems at all.

You could use SQL Server replication to replicate inserts and updates to a remote machine, but basically have it ignore the deletes.

Before I'd start looking at hacking together something like that I'd probably look at index tuning or query rewriting to see what I can do to resolve the performance problems with the queries that I've got.

  • On of my larger tables has 15M rows. It has been live for less than a year and will need to be live for many many years to come. The performance is acceptable for now, be we are starting to notice some slow downs. – Vaccano Apr 2 '13 at 18:04
  • Thank you for the idea on replication. I did not know it could be setup to only replicate updates and inserts. – Vaccano Apr 2 '13 at 18:04
  • It's not a normal configuration option. You'd have to edit the delete stored procedure so that the stored procedure doesn't actually do anything. – mrdenny Apr 2 '13 at 20:44

You should also look at partitioning your data. Log shipping is another option for a reporting database.

There are other factors to think about as well, such as how many rows are being retrieved, how big are the indexes? How often do you rebuild them? When you append to a table all the time the indexes get unbalanced and performance suffers.

The other thought that comes to mind is to prepare the data for a datamart and remove it from the active db. In the end what ever makes the most sense from a business perspective is likely your best answer. If business has no vision to use the old data I would still keep it, but perhaps not on line.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.