April 8, 2011 at 8:44 am
I have a very large data warehouse running on SQL 2008 and I am replicating 6 months of data to a data mart. I'm using a filter statement on the publication. My problem is that the data in the data mart is not removed once the data is over 6 months old. I guess I assumed that the data would be purged from the data mart when it no longer met the filter statement. My question is 1) Is there a mechanism in SQL to purge the data once it no longer meets the filter statement and if so is there documentation available so I can verify my filter? 2) Do I need to set up my own purge routine? If so, any suggestions?
April 10, 2011 at 10:46 pm
What if you partition the table on the date field and then you can drop the partitions you no longer want/need?
April 10, 2011 at 11:40 pm
Hi,
I had almost exactly the same problem in the past, and the only way I found out to deal with it was to clear old records from subscriber directly.
Filter is set up at the time when the initial snapshot is created. After that, only log is read and transactions from the log are applied.
Subscription data would be purged and recreated if you reinitialize replication (reckon that you are not really willing to do it )
MB
April 11, 2011 at 6:43 am
Thanks for the input. I cannot partition the data on the data mart because we are not running Enterprise Edition on the data mart, just on the data warehouse. I thought I would probably have to purge the data with a SQL job. Generating the snapshot and pushing it to the data mart again is not an option either as it takes 2 days to push the data over.
Thanks again. I think I'll go with what I thought the answer would be anyway and that is to have a job that purges the data.
April 11, 2011 at 7:06 am
Is there a mechanism in SQL to purge the data once it no longer meets the filter statement and if so is there documentation available so I can verify my filter?
I do not know how much I understood your issue. As you cannot use partition, can you use FILTERED INDEX ? So you can filter the data as per your requirement (6 months) rest will not be considered in the index.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 11, 2011 at 7:10 am
No, a filtered index does not purge (delete) the data. I want to remove the data from the data mart after 6 months.
April 11, 2011 at 7:37 am
So its probably not the most elegant solution and may not even be viable depending on your situation. but you can always have a job that runs a delete daily from the tables to drop records older than 6m.
April 11, 2011 at 7:37 am
No, a filtered index does not purge (delete) the data. I want to remove the data from the data mart after 6 months.
If you are not required the old data, you can create a DELETE script and run it as a Agent job. Is there any prob deleting data like that?
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 11, 2011 at 7:45 am
That's what I figured would be the outcome, but I was just making sure that I understood what the filtered subscription would or would not do.
Thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply