SQL Server Performance Forum – Threads Archive
Non-logged tranactions on transactional Repl
Folks, I am testing the sql server 2005 high availabilty features recently, so I had many questions, thank you in advance! I found you could not be able to perform non-logged tranactions against your publisher, but you can do them on your subscribers I set up my db full recovery mode, which means everything will be fully logged, then I issued a truncate table statement, and get the error Msg 4711, Level 16, State 1, Line 17Cannot truncate table because it is published for replication. I had a question, that the truncate table statement is actually logged, the page deallocation is logged, rather than each data row deletion, why I could not be able to run truncate table against the publisher? Is there a full list stating what kinds of statements/operations could not be able to run against a publisher? Thank you so much ——————
Bug explorer/finder/seeker/locator
——————
Non-Logged operations like BCP are logged operation when it comes to Transactional replication…because transactional replication requires a PK on table…PK comes with an index…
When there is an index on a table…BCP uses SLOW BCP not the fast… TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. Note: There is nothing in SQL server non-logged…every thing is logged…
truncate table, bcp and select into….called MINIMALLY LOGGED operations…
MohammedU.
Moderator
SQL-Server-Performance.com
]]>