none
how to purge data in transaction table or we can delete some data and store in separate table in data warehouse RRS feed

  • Question

  • hi,

    how to purge data  in transaction table or we can delete some data and store in separate table in data warehouse

    Regards,

    Manish

    Tuesday, August 18, 2015 3:55 PM

Answers

  • Table partitioning is an enterprise only feature the user is using standard edition.

    He will have to build his own custom archiving process.

    In standard edition Partition Views could be used if this was for new development or you could re-architect the table(s) that need to be archived or purged to easily change the underlining tables to help mitigate the effect on the application by archiving and purging. 

    For example, to archive or purge a table in the partitioned view just update the view to remove it and then it won't impact OLTP transactions against the view.

    https://technet.microsoft.com/en-US/library/ms190019%28v=SQL.105%29.aspx?f=255&MSPPError=-2147217396

    https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/


    Sunday, August 23, 2015 2:12 PM

All replies

  • If you want to just delete all the data in the table efficiently, just issue a TRUNCATE TABLE command against the table.  Alternatively, you can archive the data which is just a simple insert into a new table and delete from the source table.  Just make sure that you have the insert/delete in a transaction.  Another approach would be to issue a DELETE command against the source table with an OUTPUT command to the archive table.  It may consume a good amount of log space and time, but you can do it in a single statement without explicitly using a transaction.

    Hope that helps.

    Tuesday, August 18, 2015 5:12 PM
  • Manish,

    If you are using enterprise edition I would strongly recommend looking into table partitioning.  You develop an partition scheme to help you with your archiving or purging process while having little impact on your end users. 

    If this is not possible I would encourage you to build a process that would move records in batches if truncating the table is not possible for you.  This would minimize your locking and blocking during your custom archiving and purging process.

    Regards,
    John

    Tuesday, August 18, 2015 6:03 PM
  • Hi,

    i have sql server 2008R2 standard edition only

    Regards,

    Manish

    Wednesday, August 19, 2015 5:50 AM
  • hi,

    i want to know best practices  for data archiving or purging.

    Regards,

    Manish

    Wednesday, August 19, 2015 5:51 AM
  • Hi Manish,

    there isn't any built-in methods in SQL Server to archive data. You have to develop you own method to meet your requirement.

    SQL Support partitioning, where by you can move historical data to a different file group that located on an inexpensive storage. 

    http://technet.microsoft.com/en-us/library/ms190787.aspx

    http://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das

    Wednesday, August 19, 2015 6:17 AM
  • Table partitioning is an enterprise only feature the user is using standard edition.

    He will have to build his own custom archiving process.

    In standard edition Partition Views could be used if this was for new development or you could re-architect the table(s) that need to be archived or purged to easily change the underlining tables to help mitigate the effect on the application by archiving and purging. 

    For example, to archive or purge a table in the partitioned view just update the view to remove it and then it won't impact OLTP transactions against the view.

    https://technet.microsoft.com/en-US/library/ms190019%28v=SQL.105%29.aspx?f=255&MSPPError=-2147217396

    https://www.simple-talk.com/sql/sql-tools/sql-server-partitioning-without-enterprise-edition/


    Sunday, August 23, 2015 2:12 PM
  • Hi Manish,

    Let us know if this answers your question or if you have additional questions about archiving and purging.

    Regards,
    John

    Tuesday, August 25, 2015 12:14 AM