trace file doesn't exist but SQL pulling data

Question

trace file doesn't exist but SQL pulling data

Luke79

SSCrazy

Points: 2571
More actions
April 16, 2012 at 10:02 am

#260145

Here is the scenario. The file log.trc mentioned by a query below doesn't exist on server, but the query produces an output.
The following query:
SELECT CONVERT(int, t.EventClass) AS [EventClass]
,t.StartTime
, t.DatabaseName
, t.Filename
, (t.IntegerData * 8.0 / 1024) AS [ChangeInSize]
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Log\log.trc', default ) AS t
WHERE t.EventClass IN (92, 93)
order by 2 desc
An output which actually comes from a file called log_1358.trc
So not specifying the file number after "log_" assumes that it will point to the first trace file available?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

That's what the 'default' parameter says. Read all rollover files. So the very first trace file (created the first time SQL started up) would have been just called log.trc. The first rollover log_1.trc, ec, etc. SQL automatically removes older files to keep only 5.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Luk (4/16/2012)
So not specifying the file number after "log_" assumes that it will point to the first trace file available?

Correct, you do not need to provide the number.

Here is some code (originally from Aaron Bertrand) that I modified to remove the use of xp_cmdshell that derives the location of the default trace file with no number and loads it into a table...it has served me well:

SET NOCOUNT ON;

DECLARE @trace_path NVARCHAR(260);

-- get the default trace file location

SELECT @trace_path = [path]

FROM sys.traces

WHERE is_default = 1

-- remove the # suffix from the default trace file

-- location, e.g. E:\Trace\log_419.trc becomes E:\Trace\log.trc

IF @trace_path LIKE '%\_[0-9].trc' ESCAPE '\'

OR @trace_path LIKE '%\_[0-9][0-9].trc' ESCAPE '\'

OR @trace_path LIKE '%\_[0-9][0-9][0-9].trc' ESCAPE '\'

OR @trace_path LIKE '%\_[0-9][0-9][0-9][0-9].trc' ESCAPE '\'

BEGIN

SELECT @trace_path = REVERSE(@trace_path),

@trace_path = REVERSE(SUBSTRING(@trace_path, CHARINDEX('_', @trace_path) + 1, 260)) + '.trc';

END

PRINT @trace_path

IF OBJECT_ID(N'tempdb..#trace_events', 'U') > 0

DROP TABLE #trace_events;

-- load all trace files into a temp table

SELECT e.*

INTO #trace_events

FROM sys.fn_trace_gettable(@trace_path, DEFAULT) e;

ALTER TABLE #trace_events ALTER COLUMN TextData NVARCHAR(MAX)

SELECT MAX(StartTime),

MIN(starttime),

COUNT(*)

FROM #trace_events

-- SELECT * FROM #trace_events

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato