Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SQL Server 2005 Logon Triggers Expand / Collapse
Author
Message
Posted Friday, July 05, 2013 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 6, Visits: 478
I know this is a pertty aged thread, but still helps a lot to me.

One issue i running into is, how to clean up history data in Logging table on daily/hour basis? Our environment has heavy activities, 10,0000 rows inserted int Logging table per hour.

I was wandering to restart Service Broker's conversation and truncate log table, just fail to manage the conversation on the right way.

Could someone kindly shed some lights on this? I'm a idiot on Service Broker

Thanks so much!
Post #1470620
Posted Friday, July 05, 2013 9:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 9:51 PM
Points: 6, Visits: 478
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
Post #1470897
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse