Re: Profiler Trace

---

• From: "Uri Dimant" <urid@xxxxxxxxxxx>
• Date: Thu, 30 Apr 2009 08:22:08 +0300

---

Hi
Yout trace is running (should be)
- Set the trace status to start

exec sp_trace_setstatus @TraceID, 1

-- Set the trace status to stop

--exec sp_trace_setstatus 3, 0

-- Set the trace status to clear

--exec sp_trace_setstatus 3, 2



"tolcis" <nytollydba@xxxxxxxxx> wrote in message
news:42146b15-059a-475d-b903-830a092d1989@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hi,

I was able to script my profiler trace (see the script below) but when
I ran it from Query Analyzer it quickly gave me a Trace Number.
Does it mean that the trace is not running? and if it is how can I see
it?

/****************************************************/
/* Created by: SQL Profiler */
/* Date: 04/29/2009 11:03:12 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'InsertFileNameHere',
@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, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @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, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 28, @on
exec sp_trace_setevent @TraceID, 10, 34, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @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, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 28, @on
exec sp_trace_setevent @TraceID, 12, 34, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @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, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 14, 28, @on
exec sp_trace_setevent @TraceID, 14, 34, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 28, @on
exec sp_trace_setevent @TraceID, 15, 34, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 13, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 16, @on
exec sp_trace_setevent @TraceID, 17, 17, @on
exec sp_trace_setevent @TraceID, 17, 18, @on
exec sp_trace_setevent @TraceID, 17, 28, @on
exec sp_trace_setevent @TraceID, 17, 34, @on
exec sp_trace_setevent @TraceID, 17, 35, @on
exec sp_trace_setevent @TraceID, 92, 1, @on
exec sp_trace_setevent @TraceID, 92, 6, @on
exec sp_trace_setevent @TraceID, 92, 9, @on
exec sp_trace_setevent @TraceID, 92, 10, @on
exec sp_trace_setevent @TraceID, 92, 11, @on
exec sp_trace_setevent @TraceID, 92, 12, @on
exec sp_trace_setevent @TraceID, 92, 13, @on
exec sp_trace_setevent @TraceID, 92, 14, @on
exec sp_trace_setevent @TraceID, 92, 16, @on
exec sp_trace_setevent @TraceID, 92, 17, @on
exec sp_trace_setevent @TraceID, 92, 18, @on
exec sp_trace_setevent @TraceID, 92, 28, @on
exec sp_trace_setevent @TraceID, 92, 34, @on
exec sp_trace_setevent @TraceID, 92, 35, @on
exec sp_trace_setevent @TraceID, 93, 1, @on
exec sp_trace_setevent @TraceID, 93, 6, @on
exec sp_trace_setevent @TraceID, 93, 9, @on
exec sp_trace_setevent @TraceID, 93, 10, @on
exec sp_trace_setevent @TraceID, 93, 11, @on
exec sp_trace_setevent @TraceID, 93, 12, @on
exec sp_trace_setevent @TraceID, 93, 13, @on
exec sp_trace_setevent @TraceID, 93, 14, @on
exec sp_trace_setevent @TraceID, 93, 16, @on
exec sp_trace_setevent @TraceID, 93, 17, @on
exec sp_trace_setevent @TraceID, 93, 18, @on
exec sp_trace_setevent @TraceID, 93, 28, @on
exec sp_trace_setevent @TraceID, 93, 34, @on
exec sp_trace_setevent @TraceID, 93, 35, @on
exec sp_trace_setevent @TraceID, 94, 1, @on
exec sp_trace_setevent @TraceID, 94, 6, @on
exec sp_trace_setevent @TraceID, 94, 9, @on
exec sp_trace_setevent @TraceID, 94, 10, @on
exec sp_trace_setevent @TraceID, 94, 11, @on
exec sp_trace_setevent @TraceID, 94, 12, @on
exec sp_trace_setevent @TraceID, 94, 13, @on
exec sp_trace_setevent @TraceID, 94, 14, @on
exec sp_trace_setevent @TraceID, 94, 16, @on
exec sp_trace_setevent @TraceID, 94, 17, @on
exec sp_trace_setevent @TraceID, 94, 18, @on
exec sp_trace_setevent @TraceID, 94, 28, @on
exec sp_trace_setevent @TraceID, 94, 34, @on
exec sp_trace_setevent @TraceID, 94, 35, @on
exec sp_trace_setevent @TraceID, 95, 1, @on
exec sp_trace_setevent @TraceID, 95, 6, @on
exec sp_trace_setevent @TraceID, 95, 9, @on
exec sp_trace_setevent @TraceID, 95, 10, @on
exec sp_trace_setevent @TraceID, 95, 11, @on
exec sp_trace_setevent @TraceID, 95, 12, @on
exec sp_trace_setevent @TraceID, 95, 13, @on
exec sp_trace_setevent @TraceID, 95, 14, @on
exec sp_trace_setevent @TraceID, 95, 16, @on
exec sp_trace_setevent @TraceID, 95, 17, @on
exec sp_trace_setevent @TraceID, 95, 18, @on
exec sp_trace_setevent @TraceID, 95, 28, @on
exec sp_trace_setevent @TraceID, 95, 34, @on
exec sp_trace_setevent @TraceID, 95, 35, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'


-- 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

.

---

• References:
  • Profiler Trace
    • From: tolcis

• Prev by Date: Re: Question about restore database and create new filegroups
• Next by Date: Re: DB Backup Job for DB Maintenace Plan is not working
• Previous by thread: Profiler Trace
• Next by thread: Profiler Trace
• Index(es):
  • Date
  • Thread

---

Relevant Pages Re: SP_Trace_create ... blackbox trace status. ... -- If @on is zero then stop and delete the blackbox trace. ... declare @traceid int, ... exec sp_trace_setstatus @traceid, 2 -- delete blackbox trace definition ... (microsoft.public.sqlserver.tools) Re: SQL Server 2005 - Server side trace script ... By default Profiler will not have the TextData column checked for the RPC events since it can get that information from the binary data column which is usually more efficient. ... To make things consistent I usually just include the textdata column and exclude the binary when I create the trace. ... The problem I have is that when I open the trace output in Profiler, I don't see the TextData column - instead, I see the BinaryData column. ... declare @TraceID int ... (microsoft.public.sqlserver.tools) Re: I want to monitor users logon by using next trace (below), but trace file is empty! ... Vyas, MVP ... you have to stop and close that trace. ... See SQL Server Books Online for more information. ... declare @TraceID int ... (microsoft.public.sqlserver.server) Re: Profiler Bug viewing trace flat file? ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata field ... (microsoft.public.sqlserver.tools) Re: SQL Trace ... Vyas, MVP (SQL Server) ... > The following I want from SQL Trace. ... > Declare @vchrFileName NVarchar ... > Exec SP_Trace_SetStatus @intTraceIdOut,2 ... (microsoft.public.sqlserver.server)

---

• Windows
• Science
• Usenet

• Archive
• About
• Privacy
• Search
• Imprint

www.tech-archive.net  > Archive  > SQL-Server  > microsoft.public.sqlserver.server  > 2009-04