5 ways to track Database Schema changes - Part 1 - Default Trace

In the perfect world, only Database Administrators have access to SQL Server databases. All database schema changes go through strict Change Management Process where they need to be well described and approved by Change Advisory Board. The database schema is stored in a source code repository and deployed version doesn’t drift unexpectedly from its original model.

Unfortunately, we don’t live in the perfect world…

Despite the version control system, change management process, and limited access to the server, sometimes database schema is changed without our knowledge and supervision. It may happen in a development environment where a bunch of people have access and deployment process is not very strict (the common case). However, it also may happen in higher level environments where only limited number of people have access (the rare case – but not impossible).

Sooner or later such unexpected schema changes start to be very problematic. They may break some functionality or create some other issues (ie. performance degradation). They may block deployment of next changes. They simply may be implemented in an inefficient way or even just be stupid.

In such a case, various questions arise and you as a DBA will have to answer some of them.

When XYZ object was changed?
What modifications were applied to the XYZ object?
Who changed XYZ object?

In this short 5 part series, I will demonstrate you 5 different ways to track such database schema changes. Thanks to this, you always will be able to easily answer such questions. Today’s post describes how to use Default Trace.

Test Case

To demonstrate you the advantages and drawbacks of various database schema change logging methods I prepared a very simple test case. In this test, I create the following database objects: database, table, constraint, index, trigger, function, procedure, temporary table. As a next step, I modify the procedure, the trigger, and the function. Finally, I drop objects one by one.

USE master;
GO

CREATE DATABASE TrackMyChanges;
GO

USE TrackMyChanges;
GO

CREATE TABLE dbo.NewTable (
    Id int IDENTITY NOT NULL,
    Col1 varchar(20) NULL,
    Col2 varchar(20) NULL
);

ALTER TABLE dbo.NewTable
ADD CONSTRAINT pk_NewTable PRIMARY KEY CLUSTERED (Id);

CREATE INDEX ix_NewTable_Col1 ON dbo.NewTable (Col1);
GO

CREATE TRIGGER dbo.tr_NewTableIns
ON dbo.NewTable
AFTER INSERT
AS
BEGIN
    SELECT 'Version 1';
END
GO

CREATE PROCEDURE dbo.usp_NewProc
AS
BEGIN
    SELECT 'Version 1';
END
GO

CREATE FUNCTION dbo.fns_NewFun()
RETURNS varchar(20)
AS
BEGIN
    RETURN 'Version 1';
END
GO

ALTER TRIGGER dbo.tr_NewTableIns
ON dbo.NewTable
AFTER INSERT
AS
BEGIN
    SELECT 'Version 2';
END
GO

ALTER PROCEDURE dbo.usp_NewProc
AS
BEGIN
    SELECT 'Version 2';
END
GO

ALTER FUNCTION dbo.fns_NewFun()
RETURNS varchar(20)
AS
BEGIN
    RETURN 'Version 2';
END
GO

DROP TRIGGER dbo.tr_NewTableIns;
GO

DROP PROCEDURE dbo.usp_NewProc;
GO

DROP FUNCTION dbo.fns_NewFun;
GO

DROP INDEX ix_NewTable_Col1 ON dbo.NewTable;
GO

ALTER TABLE dbo.NewTable
DROP CONSTRAINT pk_NewTable;

DROP TABLE dbo.NewTable;

USE master;
GO

DROP DATABASE TrackMyChanges;

I will use this as an example of changes that may be executed in one of your environments.

Default Trace

The first and probably the easiest way to use is the Default Trace. Default Trace was introduced in SQL Server 2005. It is an internal, server-side SQL trace which as the name suggests is created and enabled by default on every SQL Server instance. You can very easily check if it is running on your instance using this simple query:

SELECT * FROM sys.traces WHERE is_default = 1;

Status = 1 means that trace is running. You can also see that it is configured to have a maximum of 5 rollover files 20 megabytes each.

If it is disabled it can be easily enabled using sp_configure. Instance reboot is not required.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE

EXEC sp_configure 'default trace enabled', 1;
RECONFIGURE

Default trace collects details of 34 events and stores them in the *.trc files in default SQL Server Instance Log directory. This query will list all 34 events captured by this trace.

SELECT  tc.category_id AS CategoryId,
    tc.name AS CategoryName,
    tei.eventid AS EventId,
    te.name AS EventName
FROM sys.traces t 
CROSS APPLY (SELECT DISTINCT eventid FROM fn_trace_geteventinfo(t.id)) tei
INNER JOIN sys.trace_events te ON tei.eventid = te.trace_event_id
INNER JOIN sys.trace_categories tc ON te.category_id = tc.category_id
WHERE t.is_default = 1;

Events collected by this trace are very interesting and useful. Some of them are related to the database files’ sizes like Auto Grow or Auto Shrink. Other capture errors and warnings. There is also a big group of Security Audit events. However, for us the most important are three events from Objects category:

Object:Created
Object:Deleted
Object:Altered

Thanks to them, Default Trace logs every situation when database objects are created, modified, or deleted.

How to read data from Default Trace?

There are 3 ways to read data collected by Default Trace.

Using T-SQL

For this purpose, we can use the fn_trace_gettablesystem function. It takes two parameters. The first one is a path to the trace file we want to read. The second one is the number of trace files we want to load (put here DEFAULT to load all available rollover files).

DECLARE @file varchar(255);
SELECT @file = path FROM sys.traces WHERE is_default = 1;

SELECT * FROM sys.fn_trace_gettable(@file, DEFAULT);

As a result, you will get a resultset with all events captured by this trace that are stored in available files. It’s hard to review such a huge number of files so I created a query that filters events related only to the object changes.

DECLARE @file varchar(255);
SELECT @file = path FROM sys.traces WHERE is_default = 1;

WITH TraceCTE AS (
    SELECT DatabaseID, 
        DatabaseName, 
        LoginName, 
        HostName, 
        ApplicationName, 
        SPID,
        StartTime,
        LEAD(StartTime) OVER (PARTITION BY XactSequence ORDER BY EventSequence) AS EndTime,
        EventClass,
        CASE EventClass
            WHEN 46 THEN 'Object:Created'
            WHEN 47 THEN 'Object:Deleted'
            WHEN 164 THEN 'Object:Altered'
            ELSE CAST(EventClass AS VARCHAR(max))
        END AS EventClassDesc,
        ObjectType,
        CASE ObjectType
            WHEN 8259 THEN 'Check Constraint'
            WHEN 8260 THEN 'Default (constraint or standalone)'
            WHEN 8262 THEN 'Foreign-key Constraint'
            WHEN 8272 THEN 'Stored Procedure'
            WHEN 8274 THEN 'Rule'
            WHEN 8275 THEN 'System Table'
            WHEN 8276 THEN 'Trigger on Server'
            WHEN 8277 THEN '(User-defined) Table'
            WHEN 8278 THEN 'View'
            WHEN 8280 THEN 'Extended Stored Procedure'
            WHEN 16724 THEN 'CLR Trigger'
            WHEN 16964 THEN 'Database'
            WHEN 16975 THEN 'Object'
            WHEN 17222 THEN 'FullText Catalog'
            WHEN 17232 THEN 'CLR Stored Procedure'
            WHEN 17235 THEN 'Schema'
            WHEN 17475 THEN 'Credential'
            WHEN 17491 THEN 'DDL Event'
            WHEN 17741 THEN 'Management Event'
            WHEN 17747 THEN 'Security Event'
            WHEN 17749 THEN 'User Event'
            WHEN 17985 THEN 'CLR Aggregate Function'
            WHEN 17993 THEN 'Inline Table-valued SQL Function'
            WHEN 18000 THEN 'Partition Function'
            WHEN 18002 THEN 'Replication Filter Procedure'
            WHEN 18004 THEN 'Table-valued SQL Function'
            WHEN 18259 THEN 'Server Role'
            WHEN 18263 THEN 'Microsoft Windows Group'
            WHEN 19265 THEN 'Asymmetric Key'
            WHEN 19277 THEN 'Master Key'
            WHEN 19280 THEN 'Primary Key'
            WHEN 19283 THEN 'ObfusKey'
            WHEN 19521 THEN 'Asymmetric Key Login'
            WHEN 19523 THEN 'Certificate Login'
            WHEN 19538 THEN 'Role'
            WHEN 19539 THEN 'SQL Login'
            WHEN 19543 THEN 'Windows Login'
            WHEN 20034 THEN 'Remote Service Binding'
            WHEN 20036 THEN 'Event Notification on Database'
            WHEN 20037 THEN 'Event Notification'
            WHEN 20038 THEN 'Scalar SQL Function'
            WHEN 20047 THEN 'Event Notification on Object'
            WHEN 20051 THEN 'Synonym'
            WHEN 20307 THEN 'Sequence'
            WHEN 20549 THEN 'End Point'
            WHEN 20801 THEN 'Adhoc Queries which may be cached'
            WHEN 20816 THEN 'Prepared Queries which may be cached'
            WHEN 20819 THEN 'Service Broker Service Queue'
            WHEN 20821 THEN 'Unique Constraint'
            WHEN 21057 THEN 'Application Role'
            WHEN 21059 THEN 'Certificate'
            WHEN 21075 THEN 'Server'
            WHEN 21076 THEN 'Transact-SQL Trigger'
            WHEN 21313 THEN 'Assembly'
            WHEN 21318 THEN 'CLR Scalar Function'
            WHEN 21321 THEN 'Inline scalar SQL Function'
            WHEN 21328 THEN 'Partition Scheme'
            WHEN 21333 THEN 'User'
            WHEN 21571 THEN 'Service Broker Service Contract'
            WHEN 21572 THEN 'Trigger on Database'
            WHEN 21574 THEN 'CLR Table-valued Function'
            WHEN 21577 THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
            WHEN 21581 THEN 'Service Broker Message Type'
            WHEN 21586 THEN 'Service Broker Route'
            WHEN 21587 THEN 'Statistics'
            WHEN 21825 THEN 'User'
            WHEN 21827 THEN 'User'
            WHEN 21831 THEN 'User'
            WHEN 21843 THEN 'User'
            WHEN 21847 THEN 'User'
            WHEN 22099 THEN 'Service Broker Service'
            WHEN 22601 THEN 'Index'
            WHEN 22604 THEN 'Certificate Login'
            WHEN 22611 THEN 'XMLSchema'
            WHEN 22868 THEN 'Type'
            ELSE CAST(ObjectType AS VARCHAR(max))
        END AS ObjectTypeDesc,
        ObjectID, 
        ObjectName,
        EventSubClass
    FROM sys.fn_trace_gettable(@file, DEFAULT)
    WHERE EventClass IN (46,47,164)
    AND ApplicationName <> 'SQLServerCEIP' --Telemetry
)
SELECT * FROM TraceCTE 
WHERE EventSubClass = 0
ORDER BY StartTime;

Thanks to this we’re able to quickly get the latest object modifications.

Using Profiler

The second way to review data collected by Default Trace is to open *.trc files using SQL Server Profiler. To do this, start Profiler application, then in the menu go to File, chose Open and then Trace File… Select the trace file you want to open. If you have permission issues, try to run Profiler as Administrator.

This solution has two main disadvantages. First one, you need to open every file separately. In most cases, you don’t know which file contains events you’re looking for. The second, Profiler has a quite inconvenient filtering functionality that can be used only on already saved traces.

Using Schema Changes History report

The third way is to use the Schema Changes History report. It is one of the SQL Server Management Studio built-in reports. You can get to this report on database or instance level. In my case, the database has already been dropped so I will use it from the instance level. Right-click on the instance, choose Reports, then Standard Reports, and finally select Schema Changes History.

As a result, you will get such a simple report:

What was captured by Default Trace?

Default Trace has captured all Object Schema modifications but without many details. Analyzing the trace files, we can find out which databases have been modified. We know the names of objects that were created, modified or dropped. We also know who executed changes and when. Unfortunately, we don’t know exactly what was done.

For object creation event, we don’t know what was its definition. For object alteration, we don’t know what was its original definition and what is the new one. Sometimes it’s even hard to discover what was changed. For example, there is an entry for creation of Primary Key constraint but it’s logged as table modification. We don’t get any additional details, so in real case scenario, we wouldn’t know what this really was.

Data retention

Another very important aspect of Database Schema changes tracking is the history retention. As I wrote earlier, by default, this trace is configured with 5 rollover files 20MB size each. 100MB may seem to be a lot for such a tracing mechanism, but remember that not only object modification events are being logged there. In busy environments where a big number of various events are generated the 100MB may be insufficient for you because it may store just a few last days.

In addition, the Default Trace configuration cannot be modified. Fortunately, there is a workaround for that. You can create your own server-side SQL trace with the configuration you want and use it instead of Default Trace that you can disable (using sp_configure).