May 11, 2009 at 10:19 am
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.
May 11, 2009 at 10:25 am
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
May 11, 2009 at 10:37 am
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.
May 11, 2009 at 2:02 pm
Dean Jones (5/11/2009)
Also, I thought it was the other way around according to MSTo 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
May 11, 2009 at 2:09 pm
GilaMonster (5/11/2009)
Dean Jones (5/11/2009)
Also, I thought it was the other way around according to MSTo 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 ?
May 11, 2009 at 2:28 pm
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
May 11, 2009 at 2:34 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply