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?

Thanks
LVL 1
craigdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

digital_thoughtsCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
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
Delete 
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

0
MohammedUCommented:
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'
0
BrandonGalderisiCommented:
MohammedU is correct about the top.  I didn't see that you were on sql 2005.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.