asked on

Rotate data in SQL Server database

We have an internal application that is setup to track all actions and changes to the system and logs it to a separate SQL database. As you can imagine it generates a lot of data and grows quickly.

We are looking for a way to rotate this audit data. We would like to have the database delete off data that is older than a specific period of time.

I am no SQL expert but I did some digging and ran into table partitioning in SQL, where you can set it up to break up this data into smaller chunks and then truncating off chunks of data as it gets old. I think this might be doable but it seemed complicated. Is there a better/simpler way to be handling this type of data in SQL Server?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KPERS

ASKER

Thanks for the reply Jim, I considered that as an option not knowing how many rows get added each day or how long it would take to run a delete for a given period of time. So I asked one of the lead developers for the application and they said given the number of rows that get added based off each user transaction it would not be recommended to attempt a scheduled delete.

That being said I took his word that given how quickly the database grows and given my rudimentary knowledge as the "DBA" (AKA infrastructure guy playing part time DBA in a small IT shop) that he was probably correct.

I would like to avoid adding the complexity of partitioning tables if at all possible so I may recommend a different route for storing these audit logs if there really is no other option.

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KPERS

ASKER

I have thought about getting with a consultant in the past from time to time to make sure I am doing things correctly and anytime I start to get over my head. I wear too many hats to dedicate the time required to be a real DBA so thank your for the recommendation!

Jim Horn

Understand. Table partitioning is a higher skillset that can be expected for a part-timer DBA. Good luck. -Jim