Partager via

Finding and stopping rogue SQL traces

  • Article

Introduction

A common cause of mysterious performance issues are traces that have been left running. Naïve use of traces can leave many traces running—traces that are slowing down the application by consuming critical resources. This happens because the SQL Server Performance Analyzer process is killed or trace sessions forgotten. In analyzing an issue, many traces may be created and left running.

Scripts

I see this issue enough that I have created some scripts for the issue. The following query will tell you which non-default traces exist:

use [master]

select T.id as [Id],
       case T.status when 0 then N'stopped' else N'running' end as [Status],
       T.path as [Path],
       case T.is_rowset when 0 then N'false' else N'true' end as [Rowset],
       case T.is_shutdown when 0 then N'disabled' else N'enabled' end as [Shutdown option],
       T.start_time as [Start],
       T.stop_time as [Stop]
from sys.traces as T
where T.is_default <> 1;

The following query can be used to close all non-default traces it also lists their locations.

use [master];

set nocount on;

declare @result table ([Path] nvarchar(260) not null);

declare trace cursor local fast_forward for select T.id, T.path
                                            from sys.traces as T
                                            where T.is_default != 1;
                                            
open trace;

declare @id int;
declare @path nvarchar(260);

fetch next from trace
  into @id, @path;
 
while @@fetch_status = 0
begin
   execute sp_trace_setstatus
     @traceid = @id,
     @status = 0;
    
   execute sp_trace_setstatus
     @traceid = @id,
     @status = 2;
    
   if (@path is not null)
   begin
     insert into @result([Path])
       values (@path);
   end;

       fetch next from trace
         into @id, @path;
end;

close trace;
deallocate trace;

select R.[Path] as [Path]
from @result as R;

In general, the queries require view server state permission:

grant alter trace to [login name];

Comments

  • Anonymous
    July 09, 2014
    The comment has been removed