I have recently found myself responsible for the statistics database that keeps a count of how much network traffic we are passing over our sattelite. We are using SQL 6.0 , but have begun an upgrade to SQL 7.0.

My problem is that the database fills up the hardrive after about 40 days, and I have to manually delete old imformation. I would like to created a stored procedure to delete rows over 40 days old or when the database is full (either, or). This would allow the database to take care of itself without user intervention.

I would like to know the scripting for getting the current date, look for dates over 40 days old, and then delete them.

So far I have read two SQL Developers books and I have not been pointed in the right direction.

If anyother information is required I will be happy to provide it.

Regards,

Dave Stout HOT Telecommunications UK

-- Anonymous, April 23, 1999

Answers

Dave,

For performance reasons, SQL Server does not automatically keep track of the creation date of each table row. For my answer, however, I will assume that you have added the creation date information as a datetime column named StatisticsDate in your table called NetworkTraffic.

What I would suggest then, is that you create a scheduled task using the Scheduled Tasks selection from the Server pulldown menu in SQL Enterprise Manager (if you are not DBA you will probably need to have the DBA do this). This task should be scheduled to run daily, be of Type TSQL, run against your traffic database and run the command sp_DeleteOldTraffic. Previous to this you will need to define the stored procedure sp_DeleteOldTraffic to include this sql code: delete from NetworkTraffic where datediff (day, StatisticsDate, getdate()) > 40

If the code above is not sufficient to keep you within your space allocation you could shrink the kept history to 39 days or whatever works. If the network traffic is more sporadic, you can also add sql code in this stored procedure to count the number of rows in the NetworkTraffic table and delete additional rows (oldest first) to stay within your space allocation. The code would have to loop with a check of the count of rows and the deletion of the oldest day's rows for each iteration through the loop.

Hope this helps,

Eric

-- Anonymous, April 26, 1999