SQL Change Log

Question

SQL Change Log

cheshirefox

Hall of Fame

Points: 3464
More actions
February 13, 2012 at 9:28 am

#252857

SQL 2008 R2 Enterprise
Someone asked me about the 'SQL Change Log' that houses all the changes on a SQL instance. This 'Log' does NOT include data changes.
The 'Log' simply includes changes to underlying objects, like table modifications, index modifications (new or alter), trigger changes, etc...
Does anyone know if/where this exists?
Currently, I use the sys.objects.modify_date to look for changes.
If there is something better - i'd like to see it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Wiseman SSCommitted Points: 1720 More actions · Answer 1

The information is available in the default trace and you can access it via the "Schema Changes History" SSMS report. The information captured is limited and it the trace files do rollover. If you want a more complete auditing solution you can use DDL Triggers or DDL Event notifications.

I created my own solution which is available to download here:

http://www.wisesoft.co.uk/software/tsql_ddl_code_history_tool/default.aspx

Hope this helps,

David

DBA Dash - Free, open source monitoring for SQL Server

cheshirefox Hall of Fame Points: 3464 More actions · Answer 2

The 'SQL Change Report' mentioned is in fact the Schema Changes History report.

Unfortunately, I need to reduce some of the noise in the report and have more control of the data returned with a timestamp.

I checked out the video mentioned above - but was hoping for a really quick solution.

Anyone have any other ideas?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Query the default trace and apply your own filters. Do note that the default trace is not a change log for all time, it keeps 5 files of 20MB each and discards anything older.

http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Wiseman SSCommitted Points: 1720 More actions · Answer 4

This is what the schema changes report is running on my system, which you might be able to use as a starting point:

exec sp_executesql @stmt=N'begin try

declare @enable int

select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''

if @enable = 1 --default trace is enabled

begin

declare @d1 datetime;

declare @diff int;

declare @curr_tracefilename varchar(500);

declare @base_tracefilename varchar(500);

declare @indx int ;

declare @temp_trace table (

obj_name nvarchar(256) collate database_default

, database_name nvarchar(256) collate database_default

, start_time datetime

, event_class int

, event_subclass int

, object_type int

, server_name nvarchar(256) collate database_default

, login_name nvarchar(256) collate database_default

, application_name nvarchar(256) collate database_default

, ddl_operation nvarchar(40) collate database_default

);

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'';

insert into @temp_trace

select ObjectName

, DatabaseName

, StartTime

, EventClass

, EventSubClass

, ObjectType

, ServerName

, LoginName

, ApplicationName

, ''temp''

from ::fn_trace_gettable( @base_tracefilename, default )

where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2

update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46

update @temp_trace set ddl_operation = ''DROP'' where event_class = 47

update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164

select @d1 = min(start_time) from @temp_trace

set @diff= datediff(hh,@d1,getdate())

set @diff=@diff/24;

select @diff as difference

, @d1 as date

, object_type as obj_type_desc

, *

from @temp_trace where object_type not in (21587)

order by start_time desc

end

else

begin

select top 0 1 as difference, 1 as date, 1 as obj_type_desc, 1 as obj_name, 1 as dadabase_name, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as application_name, 1 as ddl_operation

end

end try

begin catch

select -100 as difference

, ERROR_NUMBER() as date

, ERROR_SEVERITY() as obj_type_desc

, ERROR_STATE() as obj_name

, ERROR_MESSAGE() as database_name

, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as application_name, 1 as ddl_operation

end catch',@params=N''

DBA Dash - Free, open source monitoring for SQL Server

Golfer22 SSCrazy Points: 2256 More actions · Answer 5

If you have the storage capacity and can deal with the performance hit of Change Data Capture, you could turn it on. Then you'd have a solid history of changes.

To learn more about CDC, go here:

http://msdn.microsoft.com/en-us/library/bb522489.aspx

SQL Change Log

Cookies on SQLServerCentral