I have created one trace with Security Audits event only and about 10 data
columns and no filter. Which runs fine SQL profiler. I just want to trace
users activity on server. But I want to keep this trace running on the
server. How can I setup to run trace on server from client. I have admin
rights on SQL 2000.

[quoted text, click to view]

[quoted text, click to view]

Create a job in the SQL Server Agent to run the trace. Define the trace
in Profiler at your client until you get the specs for the trace as you
like them. Make sure you designate that the server should run the trace
and specify a trace file (local to the server and in a folder the SQL
Server Service Account has read/write access to). You can use rollover
files if you want to. It's not necessary, but it will appear that the
file is always 0MB until the trace is stopped. Run the trace, stop it,
and then select the File - Script Trace menu option to script out the
trace. Look over the generated code and test from Query Analyzer to make
sure it is working as expected. Use the generated code in the job step.

You'll need to stop the trace manually in order to see the results. You
can write a stored procedure to do this using the sp_trace* system
procedures. It could, for example, stop the trace, rename the trace
file, and then start the job.

To read the trace table or load into a table, you need to use
fn_trace_gettable.

You cannot trace directly to a table.


--
David Gugick
Imceda Software
www.imceda.com

in addition to David's suggestion, i quickly put together a script I used to
resolve a specific problem last week - this is specific to long Running
Queries but the concept is exactly the same . . .however instead of stopping
the trace manually can include a duration in the parameters as per what I
have listed below. PS. you can review what I have there and ammend
accrodingly, the only differences will be the events you are capturing.


CREATE PROCEDURE sp_DBA_LRQ
@duration int, -- in minutes
@QueryDuration int = 5, -- in seconds
@filename nvarchar(100)= @@servername, -- script appends yyyymmddhh
@dbname sysname = null, -- the database to monitor (null = monitor all
databases)
@maxfilesize bigint = 20, -- expected size of trc file in MB
@filedir varchar(1000) = 'C:\Temp\' -- file directory - remeber to include
backslash

AS

SET NOCOUNT ON

-- Name: sp_DBA_LRQ
-- Date: 22/10/2004
-- Author: Olu Adedeji
-- Note: auto set @duration (mins)
-- auto set @filename (no extension)
-- monitor @dbname (database you wish to run the sql against)
-- The latest file will always be servername_yyyymmdd.trc
-- to use this locally set @filedir = c:\temp\ -- remember to add the '\'
at the end of the directory
-- Capture Long Running Queries(LRQ)

--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- WHAT WHO WHEN
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Version: 1.00 - Base version was originally written(sp_DBA_GatherSQL) to
-- Resolve Execution plan annomalies on prod servers Olu
Adedeji 16/12/2004
-- Version: 1.04 - customised to capture specific information for PrecisDM
Olu Adedeji 16/12/2004
-- Version: 1.05 - customised to capture long running Queries Olu
Adedeji 22/12/2004
-- I have included to capture SQLBatches and Stored Procs

-- Display Procedure information

Print 'Application: SQL Profiler'
Print 'Procedure: sp_DBA_LRQ'
Print 'Function: Capture Long Running Queries(LRQ) on ' + @@servername + '
for ' + convert(varchar,@duration) + '(mins)'
Print 'MaxFileSize: '+ convert(varchar,@maxfilesize) + 'Mb'

-- clear down temp table
if (select object_id('tempdb.dbo.##fileexists') from
tempdb.dbo.sysobjects(nolock) where name = '##fileexists') is not null
drop table ##fileexists

-- Create a Queue
declare @rc int,
@TraceID int, -- sqlassigns the trace ID
@DateTime datetime, -- trace stop time
@CurrentTime datetime, -- current time
@appendDate int, -- append datestamp
@Cmd varchar(1000),
@RenameCmd varchar(1000),
@newFileName varchar(1000)

-- auto set stoptime
set @CurrentTime = current_timestamp
set @DateTime = dateadd(mi,@duration,@CurrentTime)
--set @maxfilesize = 50 -- set max file size to 10Mb
set @filename = @filename + '_' + (select
convert(varchar,current_timestamp,112))
set @newFileName = @filename
set @filename = @filedir + @filename

-- create temp table
create table ##fileexists (FileExists int, DirectoryExists int,
ParentDirectoryExists int)

-- New file name to rename to
declare @ActualFilename varchar(100) -- actual file to rename (with .trc
extension)
set @ActualFilename = @filename + '.trc'

-- populate temp table
insert into ##fileexists
exec master.dbo.xp_fileexist @Actualfilename


-- Check that file exists and rename file or proceed if it does not exist
if (select fileexists from ##fileexists where fileexists = 1) is not null
begin
-- I have used 114 to allow for 24h time conversion (similar to how
logshipping works)
select @appendDate = (select CASE WHEN
convert(varchar,datepart(hh,current_timestamp),114) < 10 THEN '0' +
convert(varchar,datepart(hh,current_timestamp),114)
WHEN convert(varchar,datepart(hh,current_timestamp),114) >= 10 THEN
convert(varchar,datepart(hh,current_timestamp),114)
END
+ CASE WHEN convert(varchar,datepart(mi,current_timestamp),114)< 10
THEN '0' + convert(varchar,datepart(mi,current_timestamp),114)
WHEN convert(varchar,datepart(mi,current_timestamp),114)>= 10 THEN
convert(varchar,datepart(mi,current_timestamp),114)
END)
select @RenameCmd = 'rename ' + @filename + '.trc ' + @NewFileName + '_'
+ convert(varchar,@AppendDate) + '.trc'

-- display the rename command (useful if file exists)
Print @RenameCmd
exec master.dbo.xp_cmdshell @RenameCmd,NO_OUTPUT
End

exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize,
@Datetime
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1

--++++++++++++++++++++++++++++++++++++++
-- Capture RPC:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 21, @on
exec sp_trace_setevent @TraceID, 10, 26, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
--++++++++++++++++++++++++++++++++++++++
-- Capture SP:Completed
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 2, @on
exec sp_trace_setevent @TraceID, 43, 3, @on
exec sp_trace_setevent @TraceID, 43, 8, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 15, @on
exec sp_trace_setevent @TraceID, 43, 21, @on
exec sp_trace_setevent @TraceID, 43, 26, @on
exec sp_trace_setevent @TraceID, 43, 35, @on

--++++++++++++++++++++++++++++++++++++++
--Capture SQL:BatchCompleted
--++++++++++++++++++++++++++++++++++++++
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 2, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 21, @on
exec sp_trace_setevent @TraceID, 12, 26, @on