'aging' tables

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.On 10.01.2007 10:49, fireball wrote:

Quote:

Originally Posted by

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|||Uzytkownik "Robert Klemme" <shortcutter@.googlemail.comnapisal w wiadomosci

Quote:

Originally Posted by

a batch like approach is probably better, i.e. once a day

the database will be run on remote client machine and developers are not
allowed to perform scheduled tasks on it (except upgradind/servicing) - so I
suppose I need to have it contorled either from application level or
database itself..|||fireball wrote:

Quote:

Originally Posted by

Uzytkownik "Robert Klemme" <shortcutter@.googlemail.comnapisal w
wiadomosci

Quote:

Originally Posted by

>a batch like approach is probably better, i.e. once a day

>
the database will be run on remote client machine and developers are
not allowed to perform scheduled tasks on it (except
upgradind/servicing)

I dont think he wants you to run the job manually.
Create a DTS package that deletes the rows, and have it scheduled/run as a
job by the sql-server-agent whenever the client starts sql-server (since you
don't know when the database/client will be turned on, you can't use a fixed
time like "at midnight").

/jim