Table is growing too big

Posted on 2008-11-20
Last Modified: 2012-05-05
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!
Question by:dennisjameshoward
    LVL 60

    Expert Comment

    A good way to do this is to use partitioning in 2005 to easily swap out old records...
    LVL 68

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Imagine you have a shopping list of items you need to get at the grocery store. You have two options: A. Take one trip to the grocery store and get everything you need for the week, or B. Take multiple trips, buying an item at a time, to achieve t…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now