Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 Logon Triggers


SQL Server 2005 Logon Triggers

Author
Message
qingniaohappy
qingniaohappy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 504
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 BrokerSad

Thanks so much!
qingniaohappy
qingniaohappy
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 504
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search