> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> - the purge of logs ( very slow by using DBMS tools ).
Try setting up the transact SQL below my sig as a DTS job that runs
daily. It keeps our database humming along just nicely by purging data
daily. Once I set the job up, my database maintenance time dropped to
virtual zero.
You can control the amount of data kept online by changing the value in
the
SET @deleteto = DATEADD(Day, - 60, GetUTCDate())
Line to whatever value you want to keep online.
> the SQL server 2000 to handle a such amount of events.
It can handle quite a bit more than that but the indexing in the ISSED
does not make querying the db very fast when you've got a lot of data
streaming in all the time. Of course the indexes to make the queries
fast would slow down the data input which is generally more important in
an application like RealSecure.
Mike Lyman
CISSP
Microsoft Corporate Security
Incident Response
PGP KEYID 0xD7BBADAD
DECLARE @deleteto DATETIME
SET @deleteto = DATEADD(Day, - 60, GetUTCDate())
SELECT eventid INTO #tempevents FROM events WHERE eventdate < @deleteto
DELETE eventrawdata
FROM eventrawdata INNER JOIN #tempevents
ON eventrawdata.eventid = #tempevents.eventid
DELETE eventupdates
FROM eventupdates INNER JOIN #tempevents
ON eventupdates.eventid = #tempevents.eventid
DELETE eventparams
FROM eventparams INNER JOIN #tempevents
ON eventparams.eventid = #tempevents.eventid
DELETE eventresponse
FROM eventresponse INNER JOIN #tempevents
ON eventresponse.eventid = #tempevents.eventid
DELETE FROM events WHERE eventdate < @deleteto
DROP TABLE #tempevents
SELECT remoteeventid INTO #tempeventsrejected FROM eventsrejected WHERE
eventdate < @deleteto
DELETE eventupdatesrejected
FROM eventupdatesrejected INNER JOIN #tempeventsrejected
ON eventupdatesrejected.remoteeventid =
#tempeventsrejected.remoteeventid
DELETE eventparamsrejected
FROM eventparamsrejected INNER JOIN #tempeventsrejected
ON eventparamsrejected.remoteeventid = #tempeventsrejected.remoteeventid
DELETE eventresponserejected
FROM eventresponserejected INNER JOIN #tempeventsrejected
ON eventresponserejected.remoteeventid =
#tempeventsrejected.remoteeventid
DELETE FROM eventsrejected where eventdate < @deleteto
DROP TABLE #tempeventsrejected
_______________________________________________
ISSForum mailing list
[EMAIL PROTECTED]
TO UNSUBSCRIBE OR CHANGE YOUR SUBSCRIPTION, go to
https://atla-mm1.iss.net/mailman/listinfo
