mssqltips logo

Keeping your SQL Server DDL Trigger Audit Table in Check

By:   |   Updated: 2010-08-31   |   Comments (5)   |   Related: More > 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


Last Updated: 2010-08-31


get scripts

next tip button



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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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 06, 2013 - 7:47:15 AM - Murugan.B Back To Top

This artical is very useful for DBA.

Thanks

Murugan.B


Tuesday, August 31, 2010 - 4:10:37 PM - BitBangerDBA Back To Top
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
The code has been updated to reflect the above comment.


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

... = 'MyDomain\CoolAdministrator'

should be

... <> 'MyDomain\CoolAdministrator'



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools