DELETE or TRUNCATE within replicated system (2000) 1

[mutley1]

Hi Guys,

Been a while, but need some input. SQL 2000 with constant transactional replication. There is 1 table which holds about 2.1 million records, and someone has a job which clears it out, repopulates it and then updates a few date fields in it. They are using a DELETE mytbl command which will obviously cause 2.1 million transactions to be replicated over, then an insert of latest data (another 2.1 million transactions), then an update on 1 date field (guessing another 2.1 million).

Question is, how does transactional replication handle TRUNCATE? would it send a TRUNCATE comand or still do 2.1M for the clear out part?

Any suggestions welcome, as the sp_MSadd_repl_commands is often blocking the sp_MSget_repl_commands and sp_replcmds procs, which I am putting down to the addition of 6.3M commands, or am I off the mark?

Thx,

M.

 

[hmckillop]

It sounds like a shocking unperformant way of doing things to continually delete and reinsert data (why cant it be updated? or managed transactionally?

In answer to your question

You cannot use TRUNCATE TABLE on tables that:

Are referenced by a FOREIGN KEY constraint.
Participate in an indexed view.
Are published using transactional replication or merge replication

Have a look at

http://www.sqlservercentral.com/articles/delete/61387/

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mutley1]

Thanks Killop - I'll get the developers to revisit their process.