SQL Server: How to query for DDL changes from the default trace when no DDL auditing has been configured on the database

Earlier today while writing a blog on how to query the SQL default trace for a specific information, I was reminded of a queer request from my project manager, which had happened quite a long time back.

We have some small number of non-critical internal SQL instances hosting some internal applications. Someone had over a period of time made some structural changes to the tables and no change records were maintained.

One fine day, my project manager comes running to me and says “can you provide me with information on who has modified the structure of tables x, y, z in the “ABC” database on the “DF” SQL instance in the last 3 months.  “Also please can I have the data in the next 30 minutes”?

The SQL instance in question was a 32 bit 2005 standalone.

When I had a look around on the SQL instance, I found no DDL auditing was ever configured neither on database level nor on SQL instance level.

Note: DDL auditing on databases can be easily configured by creating DDL triggers for ALTER, DROP & CREATE statements.

Now I needed to give the info to my project manager, which normally should have been available from DDL audit records, within a duration of 30 minutes. Again the default trace came to my rescue.

Thank God, no one in the internal applications team had disabled the default trace.

The below query lists out who all had created or altered or deleted objects in the “ABC” database over a period of last 3 months.

IF (SELECt convert(int,value_in_use) FROM sys.configurations WHERE name = 'default trace enabled')  = 1

BEGIN
 
    DECLARE @curr_tracefilename varchar(500) ; 
    DECLARE @base_tracefilename varchar(500) ; 
    DECLARE @indx int ;

    SELECT @curr_tracefilename = path from sys.traces where is_default = 1 ; 
    SET @curr_tracefilename = reverse(@curr_tracefilename);
 
   SELECT @indx  = patindex('%\%', @curr_tracefilename) ;
    SET @curr_tracefilename = reverse(@curr_tracefilename) ;
 
    SET @base_tracefilename = left( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc' ; 
         
 
    SELECT
    te.Name as DDL_Type, 
    DB_NAme(DatabaseID) as DBName,
    HostName,
    ApplicationName,
    LoginName,ObjectType,ObjectName,ObjectID,StartTime,EventSubClass
    FROM ::fn_trace_gettable( @base_tracefilename, default ) t
    INNER JOIN sys.trace_events TE 
    ON T.EventClass = TE.trace_event_id 
    WHERE
    DB_NAme(DatabaseID) = 'ABC'
    AND
    StartTime >= '2011-01-25 17:00:00.000' AND StartTime <= '2011-04-27 19:00:00.000'
    AND
    te.Name IN ('Object:Created', 'Object:Altered', 'Object:Deleted')
 
 
END

 

Regards

Vasudev Menon

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

3 Comments on “SQL Server: How to query for DDL changes from the default trace when no DDL auditing has been configured on the database”

  1. Hi Vasudev,

    Its a great tip but the problem is that it wont give the details of the exact changes that happened overtime on an object.

  2. Yes Sachin, It does not provide with the detailes of the changes made. But the saving point was, the engineer responsible for the change was identified, proof presented to the concerned engineer on the date and time of change made and was requested to update the change process documents to reflect the changes made

Leave a Reply

Your email address will not be published. Required fields are marked *