Posted by Robert Klemme on 01/10/07 11:52

On 10.01.2007 10:49, fireball wrote:
> I'm about to create some mechanism for control both: size of given table and
> age of it's entries. I wish it to delete entries if older then X , or last n
> entries when table is bigger then Y .
> I need opinion if it will be good to use ddl trigger mechanism (SS2005), or
> maybe someone would share another solution. It is supposed to be used only
> for some tables like log table, not for each table in database, I don't want
> to the mechanism decrease performance too much though.

In that case a batch like approach is probably better, i.e. once a day
run your cleanup job. If possible you should have a CI with the
timestamp as leading column to make deleting more efficient.

The easiest is deletion by age. Deleting the oldest n records is more
difficult. If you are on SQL 2005 you may be able to create something
with an analytic function (i.e. using "row_number").

Cheers

robert

[Back to original message]