Purging SQL Databases (Full Version)

All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting



Message

rpalnik -> Purging SQL Databases (10.Mar.2006 3:56:32 PM)

I've changed over to SQL for logging and noticed that there is no automatic procedure to purge the logs.  After some experimenting, I found that running a query like this would delete records:

use isa   
go
delete from webproxylog  
where logdate = '3/4/2006' 
go

Since I don't know any SQL, I'm a little stumped on how I would turn this into a stored procedure that would run everyday and let me delete records equal to (todays date) - (some number, maybe 7 or 10 days).

Any pointers on where to start?





ISAServerTools -> RE: Purging SQL Databases (10.Mar.2006 4:03:46 PM)

Paste the following into a query window using the log database:

create procedure uspPurge as
  set nocount on
  delete from webproxylog where logdate < dateadd(dd, -7, getdate())



to create a stored procedure called uspPurge.

Then, you can use SQL Agent to schedule it to run each night.  You can change -7 to whatever you like.



rpalnik -> RE: Purging SQL Databases (10.Mar.2006 4:28:14 PM)

Thanks!!!!!



Page: [1]