Can you truncate a published table?
-
We use transactional replication.We want to archive a few tables on the publisher. We choose to use truncate rather than delete because we believe this is faster. I am thinking we can 1. disable the replication, 2. take the tables out of the article list3. archive the tables (copy first, then truncate, followed by insert back in the data needs to be kept)4. add the tables back to article list5. re-enable the replication.I am fairly new to SQL Server replication. Can anyone help me clear the ideas? Do you see any problems with the above approach. Thanks in advance.
-
You don't need to mess around with the replication. You can directly truncate the published table without problem. Think of this as the same as a delete.
-
You mean the "truncate" will be automatically replicated over to the subscriber and gets executed over there?
-
I don't beleive that replication will allow you to truncate a table.
Why don't you create a transactional/snapshot publication with the tables that you want to archive, and schedule it to run at a time different then the regular scheduled replication.
good luck
"Life is not measured by the number of breaths we take, but by the moments that take our breath away." -
Truncate table is really good because it is non logged and fast and won't expend log size, however, unfortunatly Transacational Replication doesn't allow to run "Truncate table .." as SQL Draggon said.
If that is a lot of data that you want to delete and you need to have transactional replication, then, I'd use while loop to delete every X rows or something.
for example, like this..
set rowcount 10000
while @@rowcount < 10000
begin
waitfor delay '000:00:01'
delete from
end
I put the waitfor delay to easy on the server..
-
There is a way to set up a replicated archive using transactional replication in which you can actively trim or "prune" (as we like to call it in our shop) your tables on the source database to keep it small. The way we accomplished this was through the use of Row Level Filtering on each individual table being published coupled with a series of pruning procedures that run as scheduled tasks. Let me see if I can explain this in a bit more depth.
Say for instance you have a "high traffic" (transaction) table called DailyShipments you've been writing to for several years now that is growing rather large. The first thing you do is decide how much data you want to keep in the live (source) database at any given time. For this example we'll keep the last two years in the live model from this point forward (replicating ALL transactional data into the archive) and delete everything else.
Table: DailyShipments (small data subset)
DateOfShipment CustomerId ItemName Quantity 08/08/2004 SEC Wacom GraphTab 2 08/08/2004 COS Replacement Pens 3 08/08/2004 TRT Erasers 10 ...
1) Set up a publication for the table DailyShipments without row filtering. (Replicating all rows) Allow sufficient time for the archive to sync with the source.
2) Now set up a row filter. Right click on the publication for this table and take a look at the "Filter Rows" tab (Enterprise Manager) If you only want the last 2 years worth of data your row filter should look something like this:
SELECT <published_columns> FROM [dbo].[DailyShipments] WHERE
DateOfShipment >= Convert(char(10), DateAdd(Month,-24,GetDate()), 101)
From this point forward we've essentially said that any changes made to data older than our "live" 2 year window will not be replicated to the archive. This is perfectly acceptable due to the fact that we have no desire to modify these records at all. All we want to do is delete them.
3) Now write a stored procedure like the following:
CREATE PROC dbo.sp_Prune_DailyShipments AS
DECLARE @FirstOperationalDate char(10)
SELECT @FirstOperationalDate =
Convert(char(10), DateAdd(Month,-24,GetDate()), 101)
-- Trim old records
DELETE FROM
DailyShipments
WHERE
DateOfShipment < @FirstOperationalDate
--End
4) Create a new SQL Job in Enterprise manager to run this stored procedure once a week.
Does this make sense? Let me know if this seems strange. I'll gladly try to explain it further.
-
The solution above - use an empty snapshot works - I have used this a lot. Basically we had in my last job a humongous production database which at the month end had to have all its transaction data cleared. This was being replicated to more than 20 sites round the world. What I did was to set up a second identical database with no transaction data in it on the same server as the original publisher and define it as a publisher with a snapshot pointing at the original publisher - at the month end I didnt even have to stop the original transactional replication. Run once and goodbye to the redundant data.
-
One more thing - in your application you could copy the desired data to the 'empty' database and replicate that. Second tweak - if that still leaves you a lot of data to transfer around you could run an sp on the original publisher to copy the data and as long as you have the same 'empty database on your subscribers it would save the desired data locally. Use an empty database to remove the data and then run an SP to repopulate locally - havent done this but it seems reasonable.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply