April 5, 2006 at 1:01 pm
Hi,
I need to audit the DDL activities on our server (CREATE,DROP,ALTER). I can set it up using sql profiler, but I need to run this automatically. So I created the script out of that trace. But when I run the script it does generate a .trc file, but it has 0 bytes. What's going on ?
Here's the sql.
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 04/05/2006 11:50:39 AM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- 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
exec @rc = sp_trace_create @TraceID output, 0, N'C:\prod\sqltrace', @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, 113, 1, @on
exec sp_trace_setevent @TraceID, 113, 6, @on
exec sp_trace_setevent @TraceID, 113, 11, @on
exec sp_trace_setevent @TraceID, 113, 12, @on
exec sp_trace_setevent @TraceID, 113, 14, @on
exec sp_trace_setevent @TraceID, 113, 34, @on
exec sp_trace_setevent @TraceID, 113, 35, @on
exec sp_trace_setevent @TraceID, 117, 1, @on
exec sp_trace_setevent @TraceID, 117, 6, @on
exec sp_trace_setevent @TraceID, 117, 11, @on
exec sp_trace_setevent @TraceID, 117, 12, @on
exec sp_trace_setevent @TraceID, 117, 14, @on
exec sp_trace_setevent @TraceID, 117, 34, @on
exec sp_trace_setevent @TraceID, 117, 35, @on
exec sp_trace_setevent @TraceID, 118, 1, @on
exec sp_trace_setevent @TraceID, 118, 6, @on
exec sp_trace_setevent @TraceID, 118, 11, @on
exec sp_trace_setevent @TraceID, 118, 12, @on
exec sp_trace_setevent @TraceID, 118, 14, @on
exec sp_trace_setevent @TraceID, 118, 34, @on
exec sp_trace_setevent @TraceID, 118, 35, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'tempdb'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'tempdb'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
TIA
Mohan
April 5, 2006 at 3:09 pm
As I recall it writes it out when 1 of the following occurr
SQL Server is stopped (even if is a restart)
Trace is stopped
Trace reaches @maxfilesize
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply