SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to set up a profiler trace programatically


Trying to set up a profiler trace programatically

Author
Message
Dean Jones-454305
Dean Jones-454305
SSC Eights!
SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)

Group: General Forum Members
Points: 946 Visits: 1130
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226534 Visits: 46328
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, MVP, M.Sc (Comp Sci)
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


Dean Jones-454305
Dean Jones-454305
SSC Eights!
SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)

Group: General Forum Members
Points: 946 Visits: 1130
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226534 Visits: 46328
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, MVP, M.Sc (Comp Sci)
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


Dean Jones-454305
Dean Jones-454305
SSC Eights!
SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)

Group: General Forum Members
Points: 946 Visits: 1130
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 ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226534 Visits: 46328
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, MVP, M.Sc (Comp Sci)
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


Vinay Thakur-585143
Vinay Thakur-585143
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 790
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search