By: Aaron Bertrand | 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
- Add filtering to your DDL Trigger(s) to avoid capturing irrelevant data.
- Create cleanup jobs to keep your audit tables populated with current data.
- Review the following tips and other resources:
About the author
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
