Purging your IWSS Logs in SQL Server
In my IWSS (Trend Micro), that stands for Internet Web Security Suite, I found out that because my IWSS processed millions of requests in a day, the built-in IWSS purge setting in the web console does not work. Simply because it is trying to delete too many records at a time and the office works 24×7. The solution? I created a script to do it for me.
Assuming I wanted to get rid of database records (IWSS logs) that are more than 90 days old,
I delete the old records by batch by hours…
DECLARE @DATEHOURMAX datetime
DECLARE @HOURSTOADD int
DECLARE @DATEHOURSTART datetime
DECLARE @DATEHOURSTOP datetime
SET @DATEHOURMAX = convert( datetime, ‘2007-11-22′, 120 )
– INITIALIZE VARIABLE
select top 1 @DATEHOURMAX = convert( datetime, date_field , 120)
from tb_url_usage order by date_field
select top 1 @DATEHOURSTART = convert( datetime, date_field , 120)
from tb_url_usage order by date_field
SET @DATEHOURSTOP = dateadd( day, -90, getdate())
– GET DATE 90 DAYS BACK
SET @HOURSTOADD = 0
WHILE @DATEHOURMAX < convert( datetime, @DATEHOURSTOP, 120 ) BEGIN
SET @HOURSTOADD = @HOURSTOADD + 1
SET @DATEHOURMAX =
dateadd( hour, @HOURSTOADD, @DATEHOURSTART)
print CAST(@DATEHOURMAX as varchar(20))
DELETE FROM tb_url_usage WHERE date_field < @DATEHOURMAX
WAITFOR DELAY ‘00:00:01′
– PAUSE SCRIPT FOR 1 SEC.SO OTHERS CAN RUN
END
print ‘Stop purge at: ‘
print @DATEHOURSTOP
(No Ratings Yet)
Loading ...
Leave your response!