Periodically Deleting Rows in TSQL_问答_开发者

开发者 https://www.devze.com 2023-01-23 19:16 出处：网络

I have an audit table setup which essentially mirrors one of my tables along with a date, user and command type. Here\'s how it might look like:

I have an audit table setup which essentially mirrors one of my tables along with a date, user and command type. Here's how it might look like:

AuditID      UserID Individual    modtype   user  audit_performed
1           1239      Day Meff      INSERT    db开发者_StackOverflowo   2010-11-04 14:50:56.357
2           2334      Dasdf fdlla   INSERT    dbo   2010-11-04 14:51:07.980
3           3324      Dasdf fdla    DELETE    dbo   2010-11-04 14:51:11.130
4           5009      Day Meffasdf  UPDATE    dbo   2010-11-04 14:51:12.777

Since these types of tables can get big pretty quick - I was thinking of putting in some sort of automatic delete of the older rows. So for example if I have 3 months of history - if I could delete the first month while retaining the last two. And again all of this must be automatic - I imagine once a certain date is hit, a query activates and deletes the oldest month with audit data. What is the best way to do this?

I'm using SQL Server 2005 by the way.

A SQL agent job should be fine here. You definitely don't need to do this on every single insert with a trigger. I doubt you even need to do it every day. You could schedule a job that runs once a month and clears out anything older than 2 months (so at most you'd have 3 months of data minus 1 day at any given time).

You could use SQL Server agent..you can schedule a repeating job like deleting entries from the current audit table after certain period. Here is how you would do it.

I would recommend storing the data in another table audit_archive table and deleting it from the current audit table. So, that in case you want some history you still have it and your table also doesn't get too big.

You could try a trigger every time a row is added it will clear anything older than 3 months.

You could also try SQL Agent to run a script every day that will do that.

Have you looked at using triggers? You could define a trigger to run when you add a row (on INSERT) that deletes any rows that are more than three months old.