May 13, 2008 at 5:45 pm
Hi,
I'm using SQL Server 2000 with SP4. As you all know we cannot read the active trace file in SQL Server 2000. Is there any sql command in MSSQL Server 2000 to know which is active trace file?
I'm using java to read the trace files, but when i try to read the active trace file, exception is not getting to java. I want to read only in-active trace files.
So, please let me know is there any command which will let me know about active trace file.
Thanks,
Jags
May 13, 2008 at 6:10 pm
DBCC TRACESTATUS(-1)
May 13, 2008 at 6:39 pm
You can use the below SQL to get active trace information including the prefix of the trace file. The actual trace file will have a suffix in the format of "_#.trc" where # will be a variable length number starting with 1.
selectTraceId
,TraceStatus
,TraceFileName
,FileMaxSizeMb
,StopTime
,TraceOptions
,TraceOptions.OptionDefn
FROM(SELECTTraceId
,TraceFileName
,FileMaxSizeMb
,StopTime
,TraceOptions
,TraceStatus
FROM (select TraceId
, CAST ( MAX( CASE property when 1 then Value else null end ) as integer) as TraceOptions
, CAST ( MAX( CASE property when 2 then Value else null end ) as nvarchar(245))as TraceFileName
, CAST ( MAX( CASE property when 3 then Value else null end ) as integer ) as FileMaxSizeMb
, CAST ( MAX( CASE property when 4 then Value else null end ) as datetime)as StopTime
, CAST ( MAX( CASE property when 5 then
CASE Value
WHEN 0 then 'Stopped'
WHEN 1 then 'Running'
else ''
END
ELSE ''
END ) as varchar(255) ) as TraceStatus
FROM :: fn_trace_getinfo(default)
group by TraceId
) as TS
)
AS TraceInfo
LEFT OUTER JOIN
(select 1 , N'Produces a rowset'
union allselect 2 , N'Creates a new file when max file size is reached.'
union allselect 3 , N'Create a new file when max file size is reached and produce a rowset.'
union allselect 4 , N'Shuts down the trace on an error.'
union allSelect 5 , N'Produces a rowset and shutdown on error.'
union allSELECT 6 , N'Creates a new file when max file size is reached and shuts down on an error.'
union allSELECT 7 , N'Creates a new file when max file size is reached, produces a rowset and shuts down on an error.'
union allSELECT 8 , N'Blackbox trace.'
) AS TraceOptions (OptionId ,OptionDefn)
ON TraceOptions.OptionId= TraceInfo.TraceOptions
[\code]
SQL = Scarcely Qualifies as a Language
May 13, 2008 at 6:58 pm
Hi Carl,
Thanks for wonderful solution. I've little problem in sorting to my requirement. Would you mind giving me the code, which just give me only trace file name from your posted code. Am new to SQL Server and it takes time to understand and modify it. Once again thanks a lot for the help.
Jags
May 14, 2008 at 6:13 pm
For the file name only:
select TracesRunning.value as TraceFileName
FROM :: fn_trace_getinfo(default) as TracesRunning
where TracesRunning.property = 2
SQL = Scarcely Qualifies as a Language
May 15, 2008 at 6:25 pm
Thanks a lot. You made my life easy.
Jags
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply