Post reply

T-sql code to retain a max of 15 history rows per server

  • December 7, 2007 at 4:07 pm

    #377367

    I have a history table. A history row is created each time the parent row is inserted or updated via triggers.

    Updates are frequent and we only need to retain up to 15 history rows per server for queries. I cannot go by deleting after n days because the updates can happen several times a day. If there are 17 updates on a specific server, I would like to retain just the latest 15 updates and delete the other 2 history rows.

    I plan to set up a job that runs twice a day but am struggling with the t-sql code to keep a max of 15 rows per servername.

    History table layout:

    HistoryID int

    ParentID int

    servername varchar(50)

    UpdTimestamp datetime

    Thank you much in advance.

  • December 8, 2007 at 1:13 pm

    #758319

    Assuming that HistoryID is the primary key....

    DELETE FROM HistoryTable

    WHERE HistoryID NOT IN

    (SELECT Top 15 HistoryID FROM HistoryTable

    ORDER BY UpdTimestamp DESC)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 8, 2007 at 9:03 pm

    #758350

    Thank you, Gail. Works like a charm.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply