Friday, December 5, 2008


Earlier this week I had to disable many triggers just so that we can Restore Databases and Create Databases

The triggers were set to send out alerts on DDL events but 2 issues

1. The DBA db didn't exist on that server but the trigger is relying on that db, so I found the triggers using the below scripts, and disabled them (or a simple SELECT * FROM sys.triggers would work too)

    AS [Trigger Name],
             syscomments.TEXT AS [Trigger Definition],
             --SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
             syscomments.encrypted AS [IsEncrpted]
sysobjects INNER JOIN syscomments ON =
(sysobjects.xtype = 'TR')
ORDER BY [Table Name]

2. SharePoint creating database name so long that it broke the XML column used in the DDL trigger for Creating Database, so I disabled the DDL Trigger by

1. Find the trigger name (KEY!)
Server -> Server Objects -> Triggers -> trigger_name

2. DISABLE TRIGGER trigger_name

No comments:

Post a Comment