Cannot drop or truncate tables in replication

Posted on 2013-01-09
Last Modified: 2013-01-11

We have a full load job running every night which drops and recreates tables
and load data in the olap database..and an incremental job which runs
every hour during day..
We require to implement transactional replication to this database on
another server for reporting purpose, however when replicated..
the full load job fails since the tables are used in replication and cannot be dropped.
Tried truncating instead of dropping tables, however even that does not seem to work
Can we use  sp_droparticle and sp_addarticle before dropping and after creating tables
for full load job to work.
Please advise a workaround to this problem?

Question by:Sonali Paradkar
  • 2

Expert Comment

ID: 38760375
Couple of possibilities. If the tables don't have that many rows per hour (few thousand) you can just use DELETEs and let them rep over. If its over say 5000 but under 50,000, maybe put some deletes in a loop so it will only rep over a few thousand deletes at a time in each delete. The exact number of "too many" will depend on your network speeds.

We had a similar problem before in an environment where we were running a BCP in and truncate ever 5 minutes for about 20K rows per 5 min and that was killing replication, so we changed our process to use a different database for staging and write the end results to the perm, replicated database. The staging database was not replicated. So we'd do our trunc and BCP on a database lets call it STAGE and the script or procedure did its thing and put the perm results (or you might could use a view) to the perm database that DID get replication.  In our situation, we ALSO needed this data on both the "hot" and the "warm-standby" databases, so we actually had our scripts doing it on both sides to the non-rep database on each server.

Author Comment

by:Sonali Paradkar
ID: 38760510
Well..the incremental load is not a problem since it does not drop tables,
 the only issue is with full load where it requires to drop and recreate hem.

Please could you give an example for a table which needs to be dropped and recreate it
If we have staging table where there is no replication and perform the drop and recreate
How do we propogate those changes to perm  database which is getting replicated.
The view which you suggest would also  truncate and repopulate the data which is
not supported in replication.?

Accepted Solution

virtuadept earned 500 total points
ID: 38760762
If you have some of the data that is incremental and needs to be replicated then the staging non-rep database idea isn't going to work. I guess what is the reason for dropping and full loading the entire table, ever?  And again, how big of tables are we talking about?  If this can not be done with DELETE (instead of truncate) because of too many transactions for the full load delete and inserts, and if there is a true need to do a full load on it all the time because of radical shifts in the data possibly (just guessing here since I have no idea what the data is about), then maybe this specific table(s) need to be on a non-replicated database and code the application to look at it instead of the main database and then have the process to keep it in sync by running separately on both servers. You might could get by with not recoding some of the stuff by having a view into the non-rep database from the rep database that could be used for reading at least.

So lets say right now you have table BIG_ONE with tons of data in it, say, 250K rows minimum. And that gets loaded incrementally 5k per hour, but the whole table is reloaded once a day 250k rows.

You would have to create a new table BIG_ONE on a new non-rep database STAGE, then drop the table (you'll have to turn off rep for this) and create a VIEW on the rep server named BIG_ONE that SELECT columns FROM STAGE.dbo.BIG_ONE.  That view, with the same name as the old table, will allow things that read from the table to still work. But anything that writes will have to be recoded to point at the STAGE database now, including the incremental process that runs hourly and the nightly drop and reload process.

EDIT, oh and the processes that populate BIG_ONE have to be changed to run on both of the servers now, since BIG_ONE isn't replicated anymore.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql Total query 2 27
MSSQL Speen Degradation 4 23
Generate Scripts of Schema/Data with "WHERE" clause 6 63
SQL Server 2012 r2 Make faster Temp Table 17 105
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question