info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



SQL Server Schema Change History
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-12-13









There are a couple methods of getting recent schema changes out of SQL Server without building an auditing process. The first
one is using the Default trace (assuming it is enabled) and the second one is reading the Transaction Log file. The default trace
will rollover to a new file name thus if you want to log all changes you would need to setup some automated logging process for that.




Default Trace using T-SQL:


Step 1: Check the Default Trace is enabled

sp_configure

name minimum maximum config_value run_value
default trace enabled 0 1 1 1



Step 2: Get the Default Trace file path & name

SELECT start_time, last_event_time, is_default, max_files, max_size, is_rollover, event_count, [path] FROM sys.traces where is_default=1



This default trace will keep up to 5 files before they are purged.





Step 3: Execute T-SQL (insert the file path and name from Step 2 below)

SELECT
f.StartTime as 'Record Start Time'
,f.DatabaseName
,f.objectname
,f.LoginName
,CASE f.eventclass WHEN 46 THEN convert(varchar(100),'Object:Created')
WHEN 47 THEN convert(varchar(100),'Object:Dropped')
WHEN 164 THEN convert(varchar(100),'Object:Altered')
ELSE convert(varchar(100),f.eventclass)
END AS 'Event Class'
,CASE f.EventSubClass WHEN 0 THEN convert(varchar(100),'BEGIN')
WHEN 1 THEN convert(varchar(100),'COMMIT')
WHEN 2 THEN convert(varchar(100),'ROLLBACK')
ELSE convert(varchar(100),f.EventSubClass)
END AS 'Event SubClass'
,f.NTDomainName
,f.NTUserName
,f.HostName
,f.EventSequence
,f.TransactionID
,f.XactSequence
--,f.*
FROM sys.traces t
CROSS APPLY fn_trace_gettable('E:\MSSQL12.MSSQLSERVER\MSSQL\Log\log_10677.trc', DEFAULT) f
WHERE t.is_default = 1
AND f.DatabaseName not in ('tempdb','master')
AND f.ObjectType <> '21587' --Statistics
AND EventClass IN (46,47,164)
ORDER BY f.StartTime DESC, f.EventSequence DESC







Default Trace using SSMS:


Step 1: SQL Server Management Studio - Reports - Standard Reports - Schema Changes History





Read Transaction Log File

select [Begin Time] as 'BeginTime', [Transaction Name], [Transaction SID], Operation
from fn_dblog(null, null)
where Operation in ('LOP_BEGIN_XACT')
order by 'BeginTime' desc;

Transaction SID matches to SID below:

Select [sid] from sys.syslogins where name like '%%'