Keeping your SQL Server DDL Trigger Audit Table in Check

By:   |   Comments (5)   |   Related: > Triggers


Problem

In our environment we use DDL Triggers both to audit the changes to our systems over time and to provide an easy recovery mechanism for mistakes. (You can read about the basis of our implementation in a previous tip, "DDL Triggers to Track All Database Changes.") The problem is that this audit table can grow quite large if left unchecked, and this can impact both DDL operations in general as well as your attempts to retrieve any useful information from the table after the fact.

Solution

We use various techniques to keep the audit table small. It can be a tough balance to be restrictive about what data you store in the table and also to be storing enough to be useful later. Only you can decide where to draw the line between what is relevant now (or could be later), and what is not useful at all. But I will try to provide some guidance on how to trim the fat once you've decided where that threshold is located.

Use filtering in the trigger

People often create a DDL Trigger as follows, and leave it at that:

    
CREATE DATABASE AuditDB;
GO
USE AuditDB;
GO
CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(48),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);
USE YourDatabase;
GO
CREATE TRIGGER DDLTriggerName
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData XML = EVENTDATA();
 
    DECLARE @ip varchar(48) = CONVERT(varchar(48), 
    CONNECTIONPROPERTY('client_net_address'));
 
    INSERT AuditDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END
GO

Basically they are capturing CREATE/ALTER/DROP events for *all* stored procedures and generated by all users. There are certainly cases where you can avoid logging changes for specific objects (say, a stored procedure that is currently in development and undergoing a lot of changes) or by certain users (say, if you are trying to cover your tracks :-)). Let's examine a couple of ways you could add filtering to the above DDL Trigger.

  • Prevent logging for the stored procedures "dbo.MyProc1" and "dbo.MyProc2":
USE YourDatabase;
GO
ALTER TRIGGER DDLTriggerName
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData  XML = EVENTDATA(),
        @SchemaName NVARCHAR(255) 
            = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @ObjectName NVARCHAR(255) 
            = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)');
            
    IF (@SchemaName + '.' + @ObjectName) NOT IN ('dbo.MyProc1', 'dbo.MyProc2')
    BEGIN
        
 	DECLARE @ip varchar(48) = CONVERT(varchar(48), 
	CONNECTIONPROPERTY('client_net_address'));


        INSERT AuditDB.dbo.DDLEvents
        (
            EventType,
            EventDDL,
            EventXML,
            DatabaseName,
            SchemaName,
            ObjectName,
            HostName,
            IPAddress,
            ProgramName,
            LoginName
        )
        SELECT
            @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
            @EventData,
            DB_NAME(),
            @SchemaName, 
            @ObjectName,
            HOST_NAME(),
            @ip,
            PROGRAM_NAME(),
            SUSER_SNAME();
    END
END
GO

So you only go ahead with the logging if the object in question is *not* one of the ones that you are currently working on (and you could use a table instead of hard-coding the values here). Notice also that you can re-use the local variables you created in the INSERT statement, instead of parsing the XML for those values again.

  • Prevent logging for any stored procedures in the schema "dev":
USE YourDatabase;
GO
ALTER TRIGGER DDLTriggerName
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
        @EventData  XML = EVENTDATA(),
        @SchemaName NVARCHAR(255) 
            = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)');
            
    IF @SchemaName <> "dev"
    BEGIN
        
 	DECLARE @ip varchar(48) = CONVERT(varchar(48), 
	CONNECTIONPROPERTY('client_net_address'));
        
        INSERT AuditDB.dbo.DDLEvents
        (
            EventType,
            EventDDL,
            EventXML,
            DatabaseName,
            SchemaName,
            ObjectName,
            HostName,
            IPAddress,
            ProgramName,
            LoginName
        )
        SELECT
            @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
            @EventData,
            DB_NAME(),
            @SchemaName, 
            @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
            HOST_NAME(),
            @ip,
            PROGRAM_NAME(),
            SUSER_SNAME();
    END
END
GO
  • Prevent logging when the initiator is the user "MyDomain\CoolAdministrator":
USE YourDatabase;
GO
ALTER TRIGGER DDLTriggerName
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    SET NOCOUNT ON;
    IF ORIGINAL_LOGIN() <> 'MyDomain\CoolAdministrator'
    BEGIN
        DECLARE
            @EventData XML = EVENTDATA();
        
 	DECLARE @ip varchar(48) = CONVERT(varchar(48), 
	CONNECTIONPROPERTY('client_net_address'));
  
        INSERT AuditDB.dbo.DDLEvents
        (
            EventType,
            EventDDL,
            EventXML,
            DatabaseName,
            SchemaName,
            ObjectName,
            HostName,
            IPAddress,
            ProgramName,
            LoginName
        )
        SELECT
            @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'), 
            @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
            @EventData,
            DB_NAME(),
            @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
            @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
            HOST_NAME(),
            @ip,
            PROGRAM_NAME(),
            SUSER_SNAME();
    END
END
GO

Clean up history periodically

You may want to create a job that deletes all history older than 30 days or 90 days. Or at least deletes everything except the last two rows for any specific object. You can do this using the following queries:

  • This query deletes all history older than 90 days:
 
DELETE AuditDB.dbo.DDLEvents
    WHERE EventDate < DATEADD(DAY, -90, CURRENT_TIMESTAMP);
  • And this query deletes all but the most recent two rows for each object:
 
;WITH e AS
(
    SELECT
        EventDate,
        DatabaseName,
        SchemaName,
        ObjectName,
        rn = ROW_NUMBER() OVER
        (
            PARTITION BY DatabaseName, SchemaName, ObjectName
            ORDER BY EventDate DESC
        )
    FROM
        AuditDB.dbo.DDLEvents
)
DELETE e
    WHERE rn > 2;

Disable the trigger for large operations

When deploying changes that affect a large number of objects, if both the script and the revert operation are well-known, then it can make sense to disable the DDL Trigger so you are not unnecessarily loading the audit table with data that you can already recover from without auditing at all. You can disable and re-enable the trigger as follows:

 
USE YourDatabase;
GO
DISABLE TRIGGER [DDLTriggerName ON DATABASE;
-- perform your deployment here
ENABLE TRIGGER [DDLTriggerName] ON DATABASE;

That said, it may make sense in this case to delete the audit history for all of these objects, and insert the "initial" state that you have just created through the new deployment.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, September 13, 2013 - 11:48:41 PM - lukos Back To Top (26797)

Thanks a lot for this post. I'm not a dba but this is going to be really useful to me. Thanks a lot!


Wednesday, March 6, 2013 - 7:47:15 AM - Murugan.B Back To Top (22588)

This artical is very useful for DBA.

Thanks

Murugan.B


Tuesday, August 31, 2010 - 4:10:37 PM - BitBangerDBA Back To Top (10101)
I used a different approach. We did not want to monitor development, but did for QA and production. We were also interested in all ddl events, not just stored procedure changes. I used Notification Services which puts the details of any ddl event (you can select any or all of the many events, or event groups. This puts a record in your defined queue and you specify a stored procedure to fire when this happens. Your stored procedure then processes this record by "receiving" it, which takes it out of the queue. Thus the whole event notification process is self maintaining. What I did within my stored procedure was to put my filters there, to ignore such things a tempdb events, events by PEM (Patrol), etc. When I did receive a record I wanted to process further, I obtained more details and sent and e-mail with all the information I desired to myself using the sp_cdosysmail stored procedure. You can then choose to keep or delete the e-mails as needed. You could always insert a record into an audit table, along with a timestamp and write another job to purge the audit table periodically by date.


Tuesday, August 31, 2010 - 10:31:45 AM - Admin Back To Top (10098)
The code has been updated to reflect the above comment.


Tuesday, August 31, 2010 - 9:57:48 AM - Aaron Bertrand Back To Top (10097)
There is a typo in one of the examples

... = 'MyDomain\CoolAdministrator'

should be

... <> 'MyDomain\CoolAdministrator'















get free sql tips
agree to terms