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
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 22, 2015 1:07 PM
Points: 97, Visits: 1,440
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, May 12, 2015 2:23 PM
Points: 4,254, Visits: 7,190
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
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 22, 2015 1:07 PM
Points: 97, Visits: 1,440
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, May 12, 2015 2:23 PM
Points: 4,254, Visits: 7,190
"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
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 22, 2015 1:07 PM
Points: 97, Visits: 1,440
both
Post #1429530
Posted Monday, March 11, 2013 6:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, May 12, 2015 2:23 PM
Points: 4,254, Visits: 7,190
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
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 22, 2015 1:07 PM
Points: 97, Visits: 1,440
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