Note: SQL Trace and SQL Server Profiler are deprecated and will be removed in a future version of Microsoft SQL Server. The replacement technology for them is Extended Events.
To Learn More:
https://learn.microsoft.com/en-us/sql/relational-databases/sql-trace/sql-trace?view=sql-server-ver16
SQL Server comes with a default trace configured and enabled/ON by default. There are couple ways (maybe more) you can find out whether the default trace is ON/OFF.
As of SQL Server version 2019, you cannot modify or drop the default trace but you can turn it off/on using the SP_CONFIGURE command. For example to start the default trace:
To view what events are captured in the default trace:
select
c.name EventCategory,
e.name EventName
from
(SELECT distinct eventid FROM fn_trace_geteventinfo(1)) t
inner join sys.trace_events e on e.trace_event_id = t.eventid
inner join sys.trace_categories c ON e.category_id = c.category_id
order by EventCategory, EventName
That being said, I would like to read/search events from all trace logs for this default trace, including those that were rolled over. In order to do that I will need to remove the rollover number from the name of the current trace file. For example file name log_262.trc would need to be trimmed to log.trc. Then pass that to the fn_trace_gettable along with value Default for the second parameter number_files (number of trace files to read). For example: sys.fn_trace_gettable(@trace_file_name, DEFAULT). The value of Default would make sure SQL Server reads all rollover files until it reaches the end of the trace.
Here is the complete SQL script, results are filtered to look for data and log file auto growth events but you can comment those lines out:
-- lets view the settings for the default trace
SELECT * FROM sys.traces WHERE is_default = 1
-- lets get the trace file name without the roll over number
Declare @trace_file_full_name nvarchar(1000);
Declare @trace_file_path nvarchar(1000);
Declare @trace_file_name nvarchar(1000);
SELECT @trace_file_full_name = path FROM sys.traces WHERE is_default = 1
-- Split the file name into path and just the file name parts
select
@trace_file_path = LEFT(@trace_file_full_name,LEN(@trace_file_full_name) - charindex('',reverse(@trace_file_full_name),1) + 1),
@trace_file_name = RIGHT(@trace_file_full_name, CHARINDEX('', REVERSE(@trace_file_full_name)) -1) ;
-- remove the roll over number from the trace file name
;with cte as
(
SELECT PATINDEX('%[0-9.]%', @trace_file_name) start_at, CHARINDEX('.', @trace_file_name) end_at
)
select @trace_file_name = replace(STUFF(@trace_file_name, start_at, end_at-start_at, ''), '_', '') from cte;
-- put the path and file name back together
select @trace_file_name = (@trace_file_path + @trace_file_name)
select @trace_file_name trace_file_name
-- now we can look for any information available in the trace files, current as well as rolled over ones
if @trace_file_name is null
begin
goto ErrorSection
end
select top 10000
t.DatabaseName,
e.name EventName,
t.Duration Duration_Micro_Seconds,
t.Duration / 1000000 Duration_Seconds,
t.*
from sys.fn_trace_gettable(@trace_file_name, DEFAULT) t
inner join sys.trace_events e on e.trace_event_id = t.EventClass
inner join sys.trace_categories c ON e.category_id = c.category_id
where 1=1
and e.name in ('Log File Auto Grow', 'Data File Auto Grow')
and t.DatabaseName !='tempdb'
order by t.starttime desc
goto Success
ErrorSection:
raiserror('Default trace file name could not be determined...', 16,1)
Success:
Results:
Though much belated and won't be of much use when the Trace/Profile features are completely removed in future SQL Server versions, I hope you still find the post useful.