Replication???

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Replication???

Replication???

Rate Topic

Display Mode

Topic Options

Author

Message

mxy

Posted Saturday, March 09, 2013 7:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 6, Visits: 616

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

MyDoggieJessie

Posted Monday, March 11, 2013 8:29 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,063, Visits: 3,787

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" Wink

Post #1429302

mxy

Posted Monday, March 11, 2013 1:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 6, Visits: 616

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

MyDoggieJessie

Posted Monday, March 11, 2013 1:08 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,063, Visits: 3,787

"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" Wink

Post #1429450

mxy

Posted Monday, March 11, 2013 4:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 6, Visits: 616

both

Post #1429530

MyDoggieJessie

Posted Monday, March 11, 2013 6:05 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 4:32 PM
Points: 2,063, Visits: 3,787

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" Wink

Post #1429550

mxy

Posted Monday, March 11, 2013 6:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 6, Visits: 616

thanks, i will try that option. database doesnt have CDC or fk's defined.

Post #1429552

« Prev Topic | Next Topic »

Permissions