Hi guys, just to follow up my finds. We can directly truncate the logging table to remove raw data.

To end the conersation to stop event notification, I use code below:

USE msdb;

GO

DECLARE

@conversation_handle uniqueidentifier,

@ended_count int;

SET @ended_count = 0;

DECLARE old_endpoints CURSOR LOCAL FAST_FORWARD FOR

SELECT conversation_handle

FROM sys.conversation_endpoints

WHERE

far_service IN('LoggingService');

OPEN old_endpoints;

WHILE 1 = 1

BEGIN

FETCH NEXT FROM old_endpoints INTO @conversation_handle;

IF @@FETCH_STATUS = -1 BREAK;

END CONVERSATION @conversation_handle WITH CLEANUP;

SET @ended_count = @ended_count + 1;

IF @ended_count % 10000 = 0

BEGIN

RAISERROR('Cleanup progress: %d conversations ended', 0, 1, @ended_count) WITH NOWAIT;

END

END

CLOSE old_endpoints;

DEALLOCATE old_endpoints;

And to restart it, re-create it on the target database:

CREATE EVENT NOTIFICATION Logging_Event_Notification

ON SERVER

FOR AUDIT_LOGIN, AUDIT_LOGIN_FAILED--, AUDIT_LOGOUT

TO SERVICE 'LoggingService', 'current database'

GO