February 9, 2012 at 4:00 pm
I'm getting the following error when I run this
Error:
(15 row(s) affected)
Msg 214, Level 16, State 3, Procedure sp_trace_setstatus, Line 1
Procedure expects parameter '@traceid' of type 'int'.
Script:
declare @traceid int
declare @maxfilesize bigint
declare @on bit
set @on = 1
set @maxfilesize = 50 --size in MB's
exec sp_trace_create @traceid output, 2, N'C:\myTrace\mytrace', @maxfilesize, NULL
exec sp_trace_setevent @TraceID, 114, 14, @on
exec sp_trace_setevent @TraceID, 114, 27, @on
exec sp_trace_setevent @TraceID, 114, 21, @on
exec sp_trace_setevent @TraceID, 114, 3, @on
exec sp_trace_setevent @TraceID, 114, 35, @on
exec sp_trace_setevent @TraceID, 114, 12, @on
exec sp_trace_setevent @TraceID, 114, 11, @on
exec sp_trace_setevent @TraceID, 114, 40, @on
exec sp_trace_setevent @TraceID, 114, 26, @on
exec sp_trace_setevent @TraceID, 114, 28, @on
exec sp_trace_setevent @TraceID, 114, 22, @on
exec sp_trace_setevent @TraceID, 114, 34, @on
exec sp_trace_setevent @TraceID, 114, 1, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'DBCC USEROPTIONS'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- network%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- sp_who2%'
--exclude System Databases
set @intfilter = 4
exec sp_trace_setfilter @TraceID, 3, 0, 2, @intfilter -- modified 3/22/2006
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%dbaudit'
-- exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%bbrooks'
-- exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%argent%'
-- exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%dss_sql_service'
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter
set @intfilter = 17
exec sp_trace_setfilter @TraceID, 28, 1, 0, @intfilter
set @intfilter = 18
exec sp_trace_setfilter @TraceID, 28, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 34, 0, 7, N'spt%'
exec sp_trace_setfilter @TraceID, 34, 0, 7, N'#tbl_sys%'
select * from ::fn_trace_getinfo(default)
exec sp_trace_setstatus TRACEID, 1 --start trace
February 9, 2012 at 5:12 pm
i think that last command is missing the variable:
exec sp_trace_setstatus TRACEID, 1 --start trace
--should be
exec sp_trace_setstatus @TraceID, 1 --start trace
Lowell
February 10, 2012 at 8:37 am
Oops! I missed that...Thanks Lowell for pointing that out.
But this trace stops in exactly one sec. Am I missing something here?
February 10, 2012 at 8:52 am
ok i ran your command, and i have a trace up and running...it is not stopped for me.
i had to change the path from c:\MyTrace\ to a folder for me c:\data\, but the rest was a verbatum copy paste.
here's some diagnostic queries for you.
declare
SELECT @MynewTrace = ID FROM sys.traces WHERE path = 'C:\Data\mytrace.trc'
select * from ::fn_trace_getinfo(@MynewTrace)
--peek at the trace so far:
SELECT * FROM ::fn_trace_gettable('C:\Data\mytrace.trc', default)
select
--if traceid=1 and it is NOT the default, it is the C2 level Auditing Trace.
CASE
WHEN id = 1 AND is_default = 0
THEN 'C2 level auditing trace'
WHEN id IN(1,2) AND is_default = 1
THEN 'default trace'
ELSE ' normal trace'
end as TraceType,
CASE
WHEN status = 1
THEN 'Running'
ELSE 'Stopped'
END AS TraceStatus,
'exec sp_trace_setstatus ' + convert(varchar,id) + ', 0' As StopTraceCommand,
'exec sp_trace_setstatus ' + convert(varchar,id) + ', 2' As DropTraceCommand,
* from sys.traces
your query exported out with my sp_scriptAnyTrace command, which gives some nice info and formatting:
--#################################################################################################
--Scripting trace_id 2 from server DEV223
-- Trace Last Started/Restarted on Feb 10 2012 10:44:38:720AM
-- Scripted for Analysis on Feb 10 2012 10:51:33:670AM
--#################################################################################################
--declare variables for parameterizing the command
declare @traceidout int
declare @options int
declare @path nvarchar(256)
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @stoptime datetime
declare @on bit
set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.
set @maxfilesize = 50 --size in MB
set @maxRolloverFiles = 0 --number of files; ie if 5 files, start rewriting on rollover
set @stoptime = NULL -- null if never ends, else a specific date
set @options = 2 -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE
set @path = 'C:\Data\mytrace' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting
--#################################################################################################
--create the trace
exec sp_trace_create @traceidout output, @options, @path, @maxfilesize, @stoptime, @maxRolloverFiles
--#################################################################################################
--Begin Event definitions
--#################################################################################################
exec sp_trace_setevent @traceidout,114,1,@on --Audit Schema Object Access Event,TextData
exec sp_trace_setevent @traceidout,114,3,@on --Audit Schema Object Access Event,DatabaseID
exec sp_trace_setevent @traceidout,114,11,@on --Audit Schema Object Access Event,LoginName
exec sp_trace_setevent @traceidout,114,12,@on --Audit Schema Object Access Event,SPID
exec sp_trace_setevent @traceidout,114,14,@on --Audit Schema Object Access Event,StartTime
exec sp_trace_setevent @traceidout,114,21,@on --Audit Schema Object Access Event,EventSubClass
exec sp_trace_setevent @traceidout,114,22,@on --Audit Schema Object Access Event,ObjectID
exec sp_trace_setevent @traceidout,114,26,@on --Audit Schema Object Access Event,ServerName
exec sp_trace_setevent @traceidout,114,28,@on --Audit Schema Object Access Event,ObjectType
exec sp_trace_setevent @traceidout,114,34,@on --Audit Schema Object Access Event,ObjectName
exec sp_trace_setevent @traceidout,114,35,@on --Audit Schema Object Access Event,DatabaseName
exec sp_trace_setevent @traceidout,114,40,@on --Audit Schema Object Access Event,DBUserName
--#################################################################################################
--End Event definitions
--#################################################################################################
--#################################################################################################
--begin filter definitions
--#################################################################################################
-- WHERE 1 = 1
exec sp_trace_setfilter @traceidout,1,0,7, N'DBCC USEROPTIONS' -- AND TextData NOT LIKE N'DBCC USEROPTIONS'
exec sp_trace_setfilter @traceidout,1,0,7, N'-- network%' -- AND TextData NOT LIKE N'-- network%'
exec sp_trace_setfilter @traceidout,1,0,7, N'-- sp_who2%' -- AND TextData NOT LIKE N'-- sp_who2%'
exec sp_trace_setfilter @traceidout,3,0,2,4 -- AND DatabaseID > 4
exec sp_trace_setfilter @traceidout,10,0,7, N'SQL Profiler' -- AND ApplicationName NOT LIKE N'SQL Profiler'
exec sp_trace_setfilter @traceidout,11,0,7, N'%dbaudit' -- AND LoginName NOT LIKE N'%dbaudit'
exec sp_trace_setfilter @traceidout,22,0,4,100 -- AND ObjectID >= 100
exec sp_trace_setfilter @traceidout,28,1,0,17 -- OR ObjectType = 17
exec sp_trace_setfilter @traceidout,28,1,0,18 -- OR ObjectType = 18
exec sp_trace_setfilter @traceidout,34,0,7, N'spt%' -- AND ObjectName NOT LIKE N'spt%'
exec sp_trace_setfilter @traceidout,34,0,7, N'#tbl_sys%' -- AND ObjectName NOT LIKE N'#tbl_sys%'
--#################################################################################################
---end filter definitions
--#################################################################################################
---final step
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply