filling up default trace

[murtujakhokhar]

We are have scheduled automatic capture job using sp_whoisactive and storing result into table. and this action is filling up default trace with below events. This job is scheduled to run every one minute.

Missing Join Predicate
Object:Created
Missing Column Statistics
NO STATS:([s1].[recursion], [s2].[recursion])
NO STATS:([#sessions].[recursion])
NO STATS:([s1].[recursion])

Below code stores sp_WhoIsActive output into table.

DECLARE
@destination_table VARCHAR(4000) ,
@msg NVARCHAR(1000) ;

SET @destination_table = 'WhoIsActive_' + CONVERT(VARCHAR, GETDATE(), 112) ;

DECLARE @numberOfRuns INT ;
SET @numberOfRuns = 10 ;

WHILE @numberOfRuns > 0
BEGIN;
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,@get_plans = 1,
@destination_table = @destination_table ;

SET @numberOfRuns = @numberOfRuns - 1 ;

IF @numberOfRuns > 0
BEGIN
SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' +
'Logged info. Waiting...'
RAISERROR(@msg,0,0) WITH nowait ;

WAITFOR DELAY '00:00:05'
END
ELSE
BEGIN
SET @msg = CONVERT(CHAR(19), GETDATE(), 121) + ': ' + 'Done.'
RAISERROR(@msg,0,0) WITH nowait ;
END

END ;
GO

[amachanic]

We can't do anything about the object created events - that fires every time a temp table is created. The other ones, maybe. But before we take any action, I need to ask: Why not just turn off the default trace and use something better?

[amachanic]

I'm going to close this; I don't think this is something we have much reason to solve. Please migrate to Extended Events system health (or, better, a custom session).