• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Table is growing too big

I have a table that receives data every second.  What I need to do is limit the size of the table so it stops growing ( I cannot change the time for input ). We do not need to keep this many records and I have decided on the time frame to keep. What I would like to do is create a trigger that after INSERT, DELETES the oldest row, I assume this will keep the table at the size I want. Any suggestions for code? Will the INSERT and DELETE trigger slow down the DB because its every second? Or is there another way to accomplish this with as little impact as possible. I just need a starting point!
1 Solution
A good way to do this is to use partitioning in 2005 to easily swap out old records...
Scott PletcherSenior DBACommented:
A trigger would be too much overhead.

First, cluster the table by the datetime of insert.

Then you can have a job that runs every <whatever> minutes that deletes the oldest <whatever> minutes of data.

That should reduce the impact as much as reasonably possible.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now