Infrastructure at your Service

Stéphane Haby

SQL Server Tips: Default trace enabled but no file is active…

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

TraceEnableButNotActivate01

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

TraceEnableButNotActivate02

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.
TraceEnableButNotActivate03

To be sure, I use the function fr_trace_getinfo to find the current running trace file
TraceEnableButNotActivate04

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.

 

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager