How can I stop the trace from continue to run?

Question

How can I stop the trace from continue to run?

hristo1977

SSC Eights!

Points: 848
More actions
April 9, 2014 at 3:32 am

#292077

SQL Server 2008R2
Hi,
I traced my SQL Server last night using an agent job. The trace started fine. I said it to stop at 250MB of logfile size. Its only 75MB, and now I want to stop the trace and delete the file. ( the file is still used by SQL server, checked with process explorer).
How can I find and kill the process thats responsible for the tracing?
I dont seem to find it using instance explorer.
Regards
H

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

run SELECT * FROM sys.traces.

you'll probably see two traces there; trace_id = 1 would be the default, and trace_id=2 would be your trace. look at the path column and confirm that's the name you gave your file for your trace.

assuming your trace is trace_id 2, these are the next steps:

DECLARE @TRACEID int

SET @TRACEID = 2

--exec sp_trace_setstatus @TRACEID, 0 ---stop trace, you must know the traceid to stop it

--exec sp_trace_setstatus @TRACEID, 2 ---close/delete the trace you must know the traceid to delete it

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

hristo1977 SSC Eights! Points: 848 More actions · Answer 2

hristo1977

SSC Eights!

Points: 848

April 9, 2014 at 3:42 am

#1704421

Great, worked like a charm!

Regards

H

george sibbald SSC Guru Points: 104210 More actions · Answer 3

find the trace ID (1 is normally the default trace, you can confirm which is yours from where the output is going to)

--check trace status

SELECT * FROM :: fn_trace_getinfo(default)

--stop the trace

exec sp_trace_setstatus @TraceID, 0 where@traceid is your trace

i.e.

exec sp_trace_setstatus 2, 0

--Close and then delete its definition from SQL Server.

EXEC sp_trace_setstatus @TraceID, 2

i.e.

EXEC sp_trace_setstatus 2, 2

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 4

damn this slow network!

---------------------------------------------------------------------