Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Daily Maintenance Script to delete records 45 days old

Hello All,

   I am betting this will be an easy question for some, but for me its pretty tough.  I have an application that collects all of the event logs from all the servers in the domain and dumps them into a SQL Database.  It works great but there are literally millions of records daily and the database will grow to 150 GB in a few months.  Its not a big deal when I run something like this on a daily basis from SQL Query Analyzer...

DELETE FROM [Domain Event Logs].[dbo].[Security]
WHERE DateAndTime < '4/1/2006 12:00:00 AM'

However, if I am out of the office or dont run the script over the weekend I run into problems.  I need to come up with a way to automate the deletion of old records so that I dont have to worry about it.  I have read about the "INTERVAL" command but cant get it to work properly.  The Server is 2003 Server STD and SQL 2000.  Thanks for the help.  I am assigning the max points because of the time I have wasted on the weekends doing maintenance on the database.
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
DELETE FROM [Domain Event Logs].[dbo].[Security]
WHERE DateAndTime < dateadd( day, -45, getdate())

to get this scheduled, use the SQL Server agent jobs, schedule that SQL as a step to run every day ...
You can create a job with the SQL you have and schedule it to run.
thelink12Author Commented:
Perfect.  Thanks so much for the answer and quick response.  No more weekend maintenance!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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