I discover a strange error with a customer about the default trace.
As you know, SQL Server has a default trace with a lot of events like the data and log file growth.
This trace is enabled by default and you have five .trc files located in the installation directory of SQL Server.
In my case, I search through the trace file, the growth history of data files with this query:
DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); -- Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; -- rip apart file name into pieces SET @filename = REVERSE(@filename); SET @bc = CHARINDEX('.',@filename); SET @ec = CHARINDEX('_',@filename)+1; SET @efn = REVERSE(SUBSTRING(@filename,1,@bc)); SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename))); -- set filename without rollover number SET @filename = @bfn + @efn IF EXISTS (SELECT * FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg WHERE (EventClass = 92 -- Date File Auto-grow OR EventClass = 93) -- Log File Auto-grow AND StartTime > DATEADD(dy,-7,GETDATE())) BEGIN -- If there are autogrows in the last day SELECT * INTO #temp_trc FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg WHERE (EventClass = 92 -- Date File Auto-grow OR EventClass = 93) -- Log File Auto-grow AND StartTime > DATEADD(dy,-7,GETDATE()) Select CONVERT(varchar,StartTime,102) as grow_date,DatabaseName as database_name,FileName as file_name,te.name as event_class,Duration/1000 as Duration,(tmp.IntegerData*8)/1024.0 as size_MB into #temp_trc2 from #temp_trc AS tmp INNER JOIN sys.trace_events AS te ON tmp.EventClass = te.trace_event_id SELECT grow_date,event_class,database_name,file_name,Count(Duration),Convert(int,Sum(size_MB)) , Sum(Duration) from #temp_trc2 GROUP BY grow_date,database_name,file_name,event_class ORDER BY grow_date, database_name DROP TABLE #temp_trc2 DROP TABLE #temp_trc END
But I got no result…
The first step was to verify if the default trace is enabled with the command:
SELECT * FROM sys.configurations WHERE name=’default trace enable’
It is enabled, then I check the current running trace with the view sys.traces
SELECT * FROM sys.traces
As you can see, I have no file returned by the view.
If I check directly the installation directory, I see the five trace files.
This situation is very strange….
The real question is “What can I do?”
The solution was very simple.
I disable and re-enable the default trace with the sp_configure:
EXEC sp_configure ‘show advanced options’,1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure ‘default trace enabled,0; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure ‘default trace enabled,1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure ‘show advanced options’,0; GO RECONFIGURE WITH OVERRIDE; GO
After this, I re-run the query with the view sys.traces and I see that I have a current running trace file with a start_time from now.
To be sure, I use the function fr_trace_getinfo to find the current running trace file
To prevent this problem, I add a condition:
IF (select count(*) FROM sys.traces ) >0
The entire query:
DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE @bfn VARCHAR(1000); DECLARE @efn VARCHAR(10); IF (select count(*) FROM sys.traces ) >0 BEGIN -- Get the name of the current default trace SELECT @filename = CAST(value AS NVARCHAR(1000)) FROM ::fn_trace_getinfo(DEFAULT) WHERE traceid = 1 AND property = 2; -- rip apart file name into pieces SET @filename = REVERSE(@filename); SET @bc = CHARINDEX('.',@filename); SET @ec = CHARINDEX('_',@filename)+1; SET @efn = REVERSE(SUBSTRING(@filename,1,@bc)); SET @bfn = REVERSE(SUBSTRING(@filename,@ec,LEN(@filename))); -- set filename without rollover number SET @filename = @bfn + @efn IF EXISTS (SELECT * FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg WHERE (EventClass = 92 -- Date File Auto-grow OR EventClass = 93) -- Log File Auto-grow AND StartTime > DATEADD(dy,-7,GETDATE())) BEGIN -- If there are autogrows in the last day SELECT * INTO #temp_trc FROM ::fn_trace_gettable(@filename, DEFAULT) AS ftg WHERE (EventClass = 92 -- Date File Auto-grow OR EventClass = 93) -- Log File Auto-grow AND StartTime > DATEADD(dy,-7,GETDATE()) Select CONVERT(varchar,StartTime,102) as grow_date,DatabaseName as database_name,FileName as file_name,te.name as event_class,Duration/1000 as Duration,(tmp.IntegerData*8)/1024.0 as size_MB into #temp_trc2 from #temp_trc AS tmp INNER JOIN sys.trace_events AS te ON tmp.EventClass = te.trace_event_id SELECT grow_date,event_class,database_name,file_name,Count(Duration),Convert(int,Sum(size_MB)) , Sum(Duration) from #temp_trc2 GROUP BY grow_date,database_name,file_name,event_class ORDER BY grow_date, database_name DROP TABLE #temp_trc2 DROP TABLE #temp_trc END END
After that, I can read the trace file to find all grows (Event Class 92 and 93) without an error due to the missing current running trace file.
To be sure, I add a policy to check every day if a current running trace file is in place.