[Sp3] Another reason not to use DDL Triggers

So, I've already posted about my issues using DDL triggers with Replication (http://thebakingdba.blogspot.com/2009/12/replication-and-ddl-triggers-do-not-mix.html). Well, it looks like it may have contributed to issues with an active/active cluster upgrade (2005 SP3).

Installing the patch on the first active node, we got a failure on the upgrade. The error message? "Target string size is too small to represent the XML instance". But, oddly enough, the passive (which is upgraded first) worked and was on SP3.

So we deleted the trigger (which, interestingly enough, showed that replication uses DDL triggers - which makes sense but I hadn't thought about), and ran it again.

It failed again, but this time because the passive node wasn't upgraded. Check both - and yes, SP3. Roll back and forth, everything looks good. A sucky upgrade, but we got through it.

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]
go
sp_helparticle @publication = N'publication_name', @article = 'article_name'
go
use distribution
go
sp_browsereplcmds @article_id = 77 --where 77 is the article_id from above

DDL Triggers

We're slowly starting to roll this out, based on the below code. A very well written article; our concern is on performance.

http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx


--------------------
UPDATE:
Below is the code we originally rolled out, then rolled back due to XML errors with replication (see other posts with tag DDL Triggers)

With my luck it was something stupid in my code, but I haven't gone back and looked - I'm using Event Notifications now.



--1.1 version MDB 20091119.  Removed the XML field as that's a lot of data being held for no reason.
/*
use dba_repo
If Object_ID('dba_repo.dbo.DDL_Event_Log') IS NOT NULL
DROP TABLE dbo.DDL_Event_Log
CREATE TABLE dbo.DDL_Event_Log(

ID int IDENTITY(1,1) NOT NULL,
EventTime datetime NULL,
EventType varchar(15) NULL,
LoginName VARCHAR(50),
ServerName varchar(25) NULL,
DatabaseName varchar(25) NULL,
ObjectType varchar(25) NULL,
ObjectName varchar(60) NULL,
UserName varchar(15) NULL,
CommandText varchar(max) NULL
--,Entire_Event_Data XML
)
go
*/
CREATE TRIGGER [ddltrg_Audit_Log] ON DATABASE -- Create Database DDL Trigger
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_INDEX, DROP_INDEX, ALTER_INDEX,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_USER, ALTER_USER, DROP_USER
/*
CREATE TRIGGER ddltrg_Server_Audit_Log ON ALL SERVER -- Create Database DDL Trigger
FOR
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
*/
AS
--http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx
--http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1346274,00.html for event types
--See http://msdn.microsoft.com/en-us/library/ms189871%28SQL.90%29.aspx for event types
SET NOCOUNT ON
If Object_ID('dba_repo.dbo.DDL_Event_Log') IS NOT NULL
BEGIN
DECLARE @xmlEventData XML
-- Capture the event data that is created
SET @xmlEventData = eventdata()
-- Insert information to a Event_Log table
INSERT INTO dba_repo.dbo.DDL_Event_Log
(
EventTime,
EventType,
LoginName,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
-- , Entire_Event_Data
)

SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
CONVERT(VARCHAR(60), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
-- , @xmlEventData
END