How to Find out SQL Server Configuration Changes in last Hour - SQL Server DBA Tutorial

This video explain how to find out SQL Server Configuration Changes in last hour, how to find out who and what changed in SQL Server Configurations. It shows multiple ways of looking at the SQL Server configuration changes history, what is default trace in SQL Server, how to enable and disable default trace in SQL Server, best practices to define SQL Server Configuration changes history retention period, location of default trace in SQL Server, how to read default trace event selection using T-SQL Script, how to open default trace in SQL Server Profiler and resolution to the error " Failed to open the trace, Access denied"

Script for the video: How to Find out SQL Server Configuration Changes

SELECT * 
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL12.SQLTEST\MSSQL\Log\log_56.trc', default);
GO

exec sp_executesql @stmt=N'begin try
declare @enable int;
select @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 (
                textdata nvarchar(MAX) collate database_default 
        ,       login_name sysname collate database_default
        ,       start_time datetime
        ,       event_class 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'';
        
        insert into @temp_trace
        select TextData
        ,       LoginName
        ,       StartTime
        ,       EventClass 
        from ::fn_trace_gettable( @base_tracefilename, default ) 
        where ((EventClass = 22 and Error = 15457) or (EventClass = 116 and TextData like ''%TRACEO%(%''))
        
        select @d1 = min(start_time) from @temp_trace
        
        set @diff= datediff(hh,@d1,getdate())
        set @diff=@diff/24; 

        select (row_number() over (order by start_time desc))%2 as l1
        ,       @diff as difference
        ,       @d1 as date
        ,       case event_class 
                        when 116 then ''Trace Flag '' + substring(textdata,patindex(''%(%'',textdata),len(textdata) - patindex(''%(%'',textdata) + 1) 
                        when 22 then substring(textdata,58,patindex(''%changed from%'',textdata)-60) 
                end as config_option
        ,       start_time
        ,       login_name
        ,       case event_class 
                        when 116 then ''--''
                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))
                                                                ,patindex(''%changed from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+13
                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%from%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 6) 
                end as old_value
        ,       case event_class 
                        when 116 then substring(textdata,patindex(''%TRACE%'',textdata)+5,patindex(''%(%'',textdata) - patindex(''%TRACE%'',textdata)-5)
                        when 22 then substring(substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))
                                                                ,patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata)))+3
                                                                , patindex(''%. Run%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - patindex(''%to%'',substring(textdata,patindex(''%changed from%'',textdata),len(textdata) - patindex(''%changed from%'',textdata))) - 3) 
                end as new_value
        from @temp_trace 
        order by start_time desc
end else 
begin 
        select top 0  1  as l1, 1 as difference,1 as date , 1 as config_option,1 as start_time , 1 as login_name, 1 as old_value, 1 as new_value
end
end try 
begin catch
select -100  as l1
,       ERROR_NUMBER() as difference
,       ERROR_SEVERITY() as date 
,       ERROR_STATE() as config_option
,       1 as start_time 
,       ERROR_MESSAGE() as login_name
,       1 as old_value, 1 as new_value
end catch',@params=N''

How to Find out SQL Server Configuration Changes in last Hour