Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication??? Expand / Collapse
Author
Message
Posted Saturday, March 9, 2013 7:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
My database ''ABC" is configured with snapshot to Sub 1 and transactional replication to Sub 2. last week i drop transactional replication then i want to truncate all tables data keeping snapshot publication. i was not able truncate.

Is this a bug ? after dropping snapshot replication i was able to truncate. Any clue?
Post #1428947
Posted Monday, March 11, 2013 8:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:34 AM
Points: 3,989, Visits: 7,171
As far as I know it's not a bug, it's by design - If any table/article is included in any active publication, then it cannot be truncated. From BOL "It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log; merge replication tracks changes through triggers on published tables."

For your reference:
http://msdn.microsoft.com/en-us/library/ms151740.aspx


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1429302
Posted Monday, March 11, 2013 1:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
But after dropping transactional replication keeping snapshot i am not able truncate the data from that table. In spahot we can always truncate.
Post #1429447
Posted Monday, March 11, 2013 1:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:34 AM
Points: 3,989, Visits: 7,171
"Dropping transactional replication" - exactly what did you drop? Just the publication? Subscription?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1429450
Posted Monday, March 11, 2013 4:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
both
Post #1429530
Posted Monday, March 11, 2013 6:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, November 7, 2014 9:34 AM
Points: 3,989, Visits: 7,171
Per BOL (see if any of these are true for your situation) - maybe this table has an FK relationship to another table? Or you have CDC enabled?

If that doesn't work, script out the DDL and simply drop and recreate the table.


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1429550
Posted Monday, March 11, 2013 6:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:59 PM
Points: 54, Visits: 1,145
thanks, i will try that option. database doesnt have CDC or fk's defined.
Post #1429552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse