Re: How do i maintain my database to retain only 6months transactions

[Phillip Ward]

Quote

However new regulations doesn't require me to have transaction from that long back

The new "regulations" don't require you to keep them or don't allow you to keep them?
Very different things.
If it's the former, then it's your call what to do with them. Partitioning your table might be sufficient.

Quote

I would like to get rid of prior transactions and keep only 6months old transactions.

I would suggest that you construct a query that will delete records based on a (small) date range and run it for the oldest records you want to get rid of. For example, one run of the query might delete two week's worth of records, but you can run this query many times a day, each time gradually moving the date range "forwards" until you reach the "newest" record you want to keep.

Then schedule this same query to run once a day, and that's your regular House-keeping sorted!