Delete data 7 days older

Last post 09-26-2008 12:11 AM by FrankKalis. 1 replies.
Page 1 of 1 (2 items)
Active Topics My Discussions Unanswered Sort Posts: Previous Next
  • 09-25-2008 3:06 PM

    • Fais
    • Top 150 Contributor
    • Joined on 07-30-2007
    • Posts 94

    Delete data 7 days older

    This is the table DDL:
     
    CREATE TABLE index_time
        (
          Starttime DATETIME,
          ENDtime DATETIME,
          Command VARCHAR(max),
          Dumpdate datetime default getdate()
        )
    Please answer these questions:
    1)  I want to have data in this table(index_time)  latest as 7 days (i,e,) data older than 7 days should be removed. How can i achieve this functionality.
     2) Also I want to have another column in my table which says the duration for index reorg (i.e.) performing some calculations on stattime and endtime columns.

     Please add any code if you can that would be helpful.

     Thanks in advance.

  • 09-26-2008 12:11 AM In reply to

    Re: Delete data 7 days older

    ad 1) Create a stored procedures that is scheduled to run daily (or how often you need it). The body might look something like that

    DELETE <table>
    WHERE
    DumpDate <= DATEADD(day, -7, GETDATE())

    Depending on your requirements, you might want to remove the time portion contained in GETDATE() 

    ad 2) Not sure I would want this as another column in the table as you can easily generate this information during runtime with a simple DATEDIFF(millisecond, Startime, Endtime) which obviously gives you the duration in milliseconds.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderator http://www.sql-server-performance.com
    Webmaster: http://www.insidesql.org
    View Frank Kalis's profile on LinkedIn

    XING
Page 1 of 1 (2 items)
Active Topics   My Discussions    Unanswered Posts


© 2000 - 2007 vDerivatives Limited All Rights Reserved.