October 6, 2014 at 7:49 pm
I have 2 traces running on several servers. I'm trying to write a script to shut down all traces on all servers if I need them all to stop now. I'm trying to use sp_trace_setstatus through openrowset, but have encountered numerous problems. Has anyone solved this or have any wisdom to impart?
declare @id int
declare curStopTraces cursor for
select id
from sys.traces
where path like ('%D:\Trace\ProfilerData\%')
open curStopTraces
fetch next from curStopTraces into @id
while @@fetch_status = 0
begin
set @id = (
select *
from openrowset('sqlncli', 'Server'; 'User'; 'Password', N'
select * from master.sys.traces
')
)
set @id = (
select *
from openrowset('sqlncli', 'Server'; 'User'; 'Password', N'
exec sp_trace_setstatus ' + @id + N', 0
')
)
--sp_trace_setstatus @id, 2
fetch next from curStopTraces into @id
end
close curStopTraces
deallocate curStopTraces
There is an exception to every rule, except this one...
October 6, 2014 at 8:00 pm
Basically, I'm trying exec sp_trace_setstatus from 1 server to all the rest.
There is an exception to every rule, except this one...
October 6, 2014 at 11:38 pm
Quick suggestion which doesn't use a cursor and also avoids touching the default trace which cannot be altered via the procedure. The @status = 0 turns them off, 1 turns them on.
😎
DECLARE @status INT = 0;
DECLARE @SQL_STR NVARCHAR(MAX) = CONCAT(N'USE master;',NCHAR(13),NCHAR(10),(
SELECT
CONCAT(N'EXEC sp_trace_setstatus ',ST.id,N', ', @status, NCHAR(59),NCHAR(13),NCHAR(10))
FROM master.sys.traces ST
WHERE ST.is_default = 0
AND ST.status <> @status
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'));
PRINT @SQL_STR
--EXECUTE (@SQL_STR);
October 7, 2014 at 9:09 am
Thanks, I think I can work with that.
There is an exception to every rule, except this one...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply