February 29, 2016 at 2:04 pm
Inherited a 2008 server which former DBA had traces running all over the place (instead of his Idera DM) Been dumping and removing them and have Idera back on. However I still show one trace that shows a 9876 in it that I can't delete the file (says still in use) I checked the trace flags with
select
[Status] =
case tr.[status]
when 1 THEN 'Running'
when 0 THEN 'Stopped'
end
,[Default] =
case tr.is_default
when 1 THEN 'System TRACE'
when 0 THEN 'User TRACE'
end
,[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')
,[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')
from sys.traces tr
left join sys.dm_exec_sessions se on tr.reader_spid = se.session_id
I see nothing anyplace else to look?
March 1, 2016 at 1:41 am
Hi,
That should be the default trace ! If you would like to remove it then stop it first and then try to remove it.
Something like this :
EXEC sp_trace_setstatus 1, 0 -- stops the trace
EXEC sp_trace_setstatus 1, 2 -- closes the trace
Cheers,
Robert
March 1, 2016 at 6:41 am
still no traces show in query but last night of course a new trace file. No alerts trigger wonder if there is a trace flag at startup which may be part of this, hate to waste time relearning sql 2008 but this has my attention
March 1, 2016 at 7:21 am
Check if the Default trace is enabled :
SELECT* FROM sys.configurations WHERE configuration_id = 1568;
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- > default trace enabled : run_value
-- disable the Default trace
sp_configure 'default trace enabled', 0;
GO
RECONFIGURE;
GO
March 1, 2016 at 9:17 am
that was it thanks very much
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply