Friday, December 4, 2009

Replication and DDL Triggers - DO NOT MIX

So, we had started rolling out DDL Triggers, and then today replication broke.

How? A weird ARITHABORT error trying to add a table via the GUI. Weird. So I disable the trigger and add it - at which point replication itself starts throwing the error :

Target string size is too small to represent the XML instance (Source: MSSQLServer, Error number: 6354)
Get help: http://help/6354

Well, it turns out I have an XML trigger on the TARGET database, and it can't deal with the large commands involved in transactions.

How to diagnose and find the exact commands causing problems?

use [replicated_table]
sp_helparticle @publication = N'publication_name', @article = 'article_name'
use distribution
sp_browsereplcmds @article_id = 77 --where 77 is the article_id from above