Post reply

howto delete old Data records in the right way?

  • July 8, 2011 at 12:55 am

    #253408

    Hi community.

    I have a database with around 30 gigs.

    Now i want to delete in some tables all the data which is older than 3 Years.

    If i set the Database now to simple recovery it starts to grow while i delete the tables.

    After the delete the Database has around 50 gigs. I know that i have to do a reorganisation and maybe also a reindex after the delete.

    But may you guys know a better way to delete it without having a downtime?

    At the Moment I use a simple script (DELETE FROM T_LagerBestandsHistorie WHERE DATEADD(year, 1, Add_Date) < getdate())

    Any better solutions?

    Thanks

  • July 8, 2011 at 1:39 am

    #1350173

    See if this article helps:

    http://www.sqlservercentral.com/articles/T-SQL/67898/

    -- Gianluca Sartori

  • July 8, 2011 at 11:54 am

    #1350559

    stefan.mueller 34873 (7/8/2011)

    I have a database with around 30 gigs.

    Now i want to delete in some tables all the data which is older than 3 Years.

    If i set the Database now to simple recovery it starts to grow while i delete the tables.

    After the delete the Database has around 50 gigs. I know that i have to do a reorganisation and maybe also a reindex after the delete.

    But may you guys know a better way to delete it without having a downtime?

    At the Moment I use a simple script (DELETE FROM T_LagerBestandsHistorie WHERE DATEADD(year, 1, Add_Date) < getdate())

    Any better solutions?

    What's the percentaje of data you have to purge? is it less or more than 50%?

    If you have to purge 50%+ of the data I would suggest to consider reverse purging meaning create a new table with the data you want to keep, delete old table, rename new table, create indexes and RI constraints.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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

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