Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trying to set up a profiler trace programatically Expand / Collapse
Author
Message
Posted Monday, May 11, 2009 10:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 282, Visits: 1,098
Hi All,

I tried setting up a trace with the following code

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @OutputFileName nvarchar(300)
declare @DateTime datetime

SET @OutputFileName = 'D:\Traces\DBServer_Trace_' + CONVERT(VARCHAR(20), GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')


set @DateTime = '2009-05-11 20:00:08.000'

set @maxfilesize = 50

exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @DateTime
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, 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


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

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
set @bigintfilter = 1000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter



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



I have having the following problems

The value of the trace file is constantly at 0kb even though there are queries running on the system.
The trace file only gets populated if I restart SQL server.

The code: EXEC sp_trace_setstatus @traceid = 1 , @status = 2 does not delete all the trace information as SELECT * FROM ::fn_trace_getinfo(NULL) still displays it.

Also, the most crucial bit is that I cannot stop the trace just by running EXEC sp_trace_setstatus @traceid = 1 , @status = 0. I need to have the flexibility of forcing the trace to stop without having to restart the SQL service.

Any pointers here would be appreciated.



Post #714310
Posted Monday, May 11, 2009 10:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
Dean Jones (5/11/2009)
The value of the trace file is constantly at 0kb even though there are queries running on the system.
The trace file only gets populated if I restart SQL server.

There is a buffer for the events, they don't go to file instantly. The file size does change over time.

The code: EXEC sp_trace_setstatus @traceid = 1 , @status = 2 does not delete all the trace information as SELECT * FROM ::fn_trace_getinfo(NULL) still displays it.

To remove the trace definition you need to set the status to 2 (stopped) and then set the status to 0 (close and delete)

Also, the most crucial bit is that I cannot stop the trace just by running EXEC sp_trace_setstatus @traceid = 1 , @status = 0. I need to have the flexibility of forcing the trace to stop without having to restart the SQL service.

What exactly do you mean you cannot stop it? What happens if you set the status to 0? What do you expect to happen?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #714317
Posted Monday, May 11, 2009 10:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 282, Visits: 1,098
GilaMonster (5/11/2009)
Dean Jones (5/11/2009)
The value of the trace file is constantly at 0kb even though there are queries running on the system.
The trace file only gets populated if I restart SQL server.

There is a buffer for the events, they don't go to file instantly. The file size does change over time.

The code: EXEC sp_trace_setstatus @traceid = 1 , @status = 2 does not delete all the trace information as SELECT * FROM ::fn_trace_getinfo(NULL) still displays it.

To remove the trace definition you need to set the status to 2 (stopped) and then set the status to 0 (close and delete)

Also, the most crucial bit is that I cannot stop the trace just by running EXEC sp_trace_setstatus @traceid = 1 , @status = 0. I need to have the flexibility of forcing the trace to stop without having to restart the SQL service.

What exactly do you mean you cannot stop it? What happens if you set the status to 0? What do you expect to happen?



Thanks for bringing this to my attention, I didnt know a buffer was set, I assume this is 128kb. I will try this out.

Also, I thought it was the other way around according to MS
To remove the trace definition you need to set the status to 0 (stopped) and then set the status to 2 (close and delete)

When it says deleted, what do you actually mean by this, If you mean deleted the trace definition, I can still see this by running
SELECT * FROM ::fn_trace_getinfo(NULL), even though proeprty 5 would be set to 0. I was expecting not to find anything from the table at all to give full confidence that its been totally deleted.

At the moment, the only way to confirm that its been indeed deleted is to restart sql service.

Also, just to confirm that the changing of the status doesnt work, i set it to 0 = to stop and 2 = to delete. and after doing this, I find that one cant delete the trace file, which means something is still actively writing/working wtih it.
Post #714331
Posted Monday, May 11, 2009 2:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
Dean Jones (5/11/2009)

Also, I thought it was the other way around according to MS
To remove the trace definition you need to set the status to 0 (stopped) and then set the status to 2 (close and delete)

Sorry, you're right. I read one thing and wrote something else

When it says deleted, what do you actually mean by this, If you mean deleted the trace definition, I can still see this by running
SELECT * FROM ::fn_trace_getinfo(NULL), even though proeprty 5 would be set to 0. I was expecting not to find anything from the table at all to give full confidence that its been totally deleted.


Is it the same trace? It's not perhaps another one running?
Is the setting of the status succeeding? It may not throw an error, will just return a non-zero code.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #714469
Posted Monday, May 11, 2009 2:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 282, Visits: 1,098
GilaMonster (5/11/2009)
Dean Jones (5/11/2009)

Also, I thought it was the other way around according to MS
To remove the trace definition you need to set the status to 0 (stopped) and then set the status to 2 (close and delete)

Sorry, you're right. I read one thing and wrote something else

When it says deleted, what do you actually mean by this, If you mean deleted the trace definition, I can still see this by running
SELECT * FROM ::fn_trace_getinfo(NULL), even though proeprty 5 would be set to 0. I was expecting not to find anything from the table at all to give full confidence that its been totally deleted.


Is it the same trace? It's not perhaps another one running?
Is the setting of the status succeeding? It may not throw an error, will just return a non-zero code.


Yes, its thesame trace
its not throwing an error, thats right, my problem is that if the status is set to 0 to stop it, i would expect the trace file to be populated after setting status to 0, the file is still set at 0kb for some reason and cannot be deleted.

Am I doing something wrong here ?
Post #714476
Posted Monday, May 11, 2009 2:28 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
Is the setting of the status succeeding? It may fail without throwing an error. Check the return code, is it 0?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #714501
Posted Monday, May 11, 2009 2:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 8:44 PM
Points: 1,537, Visits: 739
as we could see you are creating a trace with filters:

#exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
#set @bigintfilter = 1000
#exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
#set @intfilter = 100
#exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

which means:

you want

>>ApplicationName not like N'SQL Profiler'
>>Duration greater then or equal to 1000( milliseconds)
>> ObjectID greater then then or equal to 100

(http://technet.microsoft.com/en-us/library/ms174404.aspx
http://www.sqlservercentral.com/Forums/Topic279642-5-1.aspx)

could be this condition is not matching on your statements....

HTH.

Thanx.
Vinay



Thanx.
Vinay


http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
Post #714508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse