February 2, 2007 at 4:25 am
Hi,
Is it possible to create a trace that saves its output to a database table, without using SQL Profiler?
February 2, 2007 at 5:39 am
yes u can but u will have to script out all the required ones. read the below procs in books online this will surely help
sp_trace_create
sp_trace_generateevent (Transact-SQL)
sp_trace_setevent (Transact-SQL)
sp_trace_setfilter (Transact-SQL)
sp_trace_setstatus (Transact-SQL)
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 2, 2007 at 1:40 pm
I don't think you can write trace data directly to table using procedures...
You can write to a file and use fn_trace_gettable function to copy the trace file to table...
MohammedU
Microsoft SQL Server MVP
February 4, 2007 at 11:36 pm
Can fn_trace_gettable be used to access the current file? It seems as if SQL buffers the current trace until it reaches the file size that was specified before it writes it to disk. Only then are you able to get to the latest trace information. Is there a way around this?
February 7, 2007 at 8:01 am
Hi,
you cane make use of bleow to suit your need
I have done below for login auditing and writing to table
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @filename_prefix nvarchar(128)
declare @filename_full nvarchar(128)
declare @yr char(4)
declare @mo char(2)
declare @dy char(2)
declare @hr char(2)
declare @mi char(2)
declare @erase varchar(8000)
declare @status int
set @maxfilesize = 30
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
select @yr = convert(char(4),datepart(yyyy,getdate()))
select @mo = convert(char(2),datepart(mm,getdate()))
if len(@mo) = 1 select @mo = '0' + @mo
select @dy = convert(char(2),datepart(dd,getdate()))
if len(@dy) = 1 select @dy = '0' + @dy
select @hr = convert(char(2),datepart(hh,getdate()))
if len(@hr) = 1 select @hr = '0' + @hr
select @mi = convert(char(2),datepart(mi,getdate()))
if len(@mi) = 1 select @mi = '0' + @mi
select @filename_prefix = 'c:\str\log\trace_Audit1_' +@yr+@mo+@dy+ '_' +@hr+@mi
select 'trace file prefix: ' + @filename_prefix
exec @rc = sp_trace_create @TraceID output, 2, @filename_prefix , @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 20, 3, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 7, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
set @intfilter=100
exec sp_trace_setfilter @TraceID, 22, 1, 4, @intfilter
-- Create trace_Audit1 table if it doesn't exist
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trace_Audit1]') and OBJECTPROPERTY(id, N'IsTable') = 1)
create table [dbo].[trace_Audit1]
(
RowNumber int identity,
EventClass int,
DatabaseID int,
NTUserName nvarchar(128) null,
NTDomainName nvarchar(128) null,
HostName nvarchar(128) null,
ApplicationName nvarchar(128) null,
LoginName nvarchar(128) null,
SPID int,
StartTime datetime
)
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
waitfor delay '00:10:00'
exec sp_trace_setstatus @TraceID, 0
waitfor delay '00:00:05'
select @filename_full = @filename_prefix + '.trc'
select 'trace file full name: ' + @filename_full
insert into trace_Audit1
(EventClass, DatabaseID, NTUserName, NTDomainName, HostName, ApplicationName, LoginName,
SPID, StartTime)
select EventClass, DatabaseID, NTUserName, NTDomainName, HostName, ApplicationName, LoginName,
SPID, StartTime
from ::fn_trace_gettable(@filename_full, default)
print 'test2'
-- Erase trace file
SELECT @erase = 'erase ' + @filename_full
EXEC @status = master.dbo.xp_cmdshell @erase, no_output
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
February 7, 2007 at 4:48 pm
You can rollover the files when it reaches certain size then read all files except the one in use...
If you make rollover size is smaller ...
Note: You configure the profiler and then click on FILE/SCRIPT TRACE../FOR SQL SERVER 2000 and save it a file and open the file in QA...
MohammedU
Microsoft SQL Server MVP
February 8, 2007 at 2:19 am
Stopping and starting…I guess that is going to have to do.
Thx STReddy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply