October 7, 2011 at 10:32 am
Hi,
I started my server side trace this morning and now I used the below command to stop it.
exec sp_trace_setstatus 2,0
the result was commands executed successfully.
But I still see the trace file size as 0Kb on my drive. I dont understand why is it still 0kb. DO I need to need run any other query to release and see the correct trace file size. Please help
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 7, 2011 at 10:38 am
Also, when i query select * from sys.traces the status column shows 0 for my trace that has id as 2.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 7, 2011 at 11:10 am
Also, I set the end time to a later time in the code that I used for tracing. But had to stop trace in the middle (before actual complete time specified). I still dont know why the .trc is 0Kb
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 7, 2011 at 11:31 am
when the trace is running and active, the operating system will not be able to report the file size, so it's not suprising it's still showing 0kb;
only when a rollover occurs, a stop and start of the service occurs, or something so that there's not an exclusive lock on the file , or if the trace is stopped will you see the actual size from the OS, i think.
be careful with the exec sp_trace_setstatus traceid, 2 command, that will delete your trace,a dn it's probably not what you wanted to do.
--turn on the trace
exec sp_trace_setstatus @traceid, 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
October 7, 2011 at 3:54 pm
Lowell,
I already stopped the trace by executing
exec sp_trace_setstatus @trace_id,0
@traceid in this case is 2, and when executed I get the result command executed successfully. but the file size is still 0Kb.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 7, 2011 at 9:16 pm
What does a query of sys.traces report for event_count? If you don't have many or any events collected then the file will be small. Have you tried opening the file either using the Profiler GUI or fn_trace_gettable?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 7, 2011 at 11:53 pm
Yes, I tried to open it and it opened fine. Now I see that it shows the file size. I think its showing the size after the specified time has passed.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 8, 2011 at 6:21 am
Sapen (10/7/2011)
Yes, I tried to open it and it opened fine. Now I see that it shows the file size. I think its showing the size after the specified time has passed.
No, re-read Lowell's answer. As longs has SS doesn't no stop & close the trace, window doesn't report on the size.
The only workaround I know of is to make a copy of the trace file and that copy will show you the actual size.
April 4, 2014 at 10:55 am
If trace is default then this code is handy to disable:
SELECT @@ServerName, * FROM :: fn_trace_getinfo(default)
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC sp_configure 'default trace enabled',0
RECONFIGURE
GO
EXEC sp_configure 'show advanced options',0
RECONFIGURE
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply