[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Weekly purge of logging data

In debug mode my application logs every event in a sql server table, its turning out to be quite handy and I want to leave it on permanently but with a weekly purge.

So how do I make a task that deletes all data from this table (userlog) older than 1 week? And how do I schedule it to run every friday night at midnight?

  • 2
4 Solutions
Well, the easiest would be to create a SSIS package with the DELETE query, and then schedule the package to run on the schedule you need it to run. But you do need SSIS installed and running.
Create a stored procedure that deletes all data and then add a scheduled task to SQL Agent

There are two ways to calculate the date.  Today -7, and Last Firday like you said.
declare   @dow           int,
          @CurrDate      datetime,
          @DelDate       datetime,
          @Continue      int
set @currDate  = cast(getdate() as varchar(11))                  -- Gets the current Date
set @DOW       = (@@DATEFIRST + DATEPART(dw, GETDATE())) % 7     -- Finds out the day of week, compensating for changing the @@FirstDay parameter
set @DelDate   = dateadd(d,-7+(7-@dow),@currDate)                -- Sets the @DelDate to LAST friday, regardless of date run
set rowcount 1000
set @Continue=1
while @@rowcount>0
from Application_Log
where LogDate < @DelDate
set rowcount 0
 CAUTION... this will delete every thing older than friday of last week
 So if you run it on Sunday, it will delete everything older than 2 days old.
If you run it on Friday, it will delete anything older than LAST Friday.
If you want to delete older than 7 days ago, just use the date calculation below
set @DelDate   = dateadd(d,-7,@currDate)

Open in new window

In sql server 2005 you can use TOP command to delete 1000 rows at a time instead of using SET ROWCOUNT.

Note: Use Wait For Delay command to wait for a ms or two during each delete so that other process can process its data.
   WAITFOR DELAY '00:00:00.002'
MohammedU is correct about the top.  I didn't see that you were on sql 2005.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now