locked
Deleting data to reduce size RRS feed

  • Question

  • Hi,,,I have a table with 40 million rows...I am planning to delete the data from this table in order to reduce the db size. THis table is a heap and no index on it.

    Please let me know if deleting data will reduce the size of the db? or any other better way to handle this situation...


    Thanks, Gaurav
    Tuesday, November 16, 2010 5:15 AM

Answers

  • Hi Gaurav,

    One of the restrictions with deleting from heaps is that pages will not be deallocated unless one of the following is true (see Books Online at http://msdn.microsoft.com/en-us/library/ms189835.aspx, section "Deleting Rows from a Heap"):

    1. Specify the TABLOCK hint in your delete statement.  Note that this will lock the entire table, making it unavailable for a the duration of the delete.

    2. Use TRUNCATE TABLE to delete all rows.  No locking, no transaction logging, almost instantaneous.

    3. Create a clustered index, perform the delete, and drop the clustered index. That's Books Online's idea.  I see no reason why you can't perform the delete, then create and drop the clustered index.

     

    If you do delete rows instead of truncating, you will need space in your transaction log to record 40 million delete operations.  Make sure you have sufficient space for this.  It is worth considering doing the delete in small batches.  This reduces locking, and minimises rollback time.  (If you do the delete in a single transaction and it takes 2 hours and you then cancel it, you can expect a further 2 hours to rolback!)

    After deleting, and having the pages deallocated, the database's data file (MDF) will not have shrunk, but there will be space available.  Shrinking the data file will reclaim this space, but will result in a fragmented database.  If your database is likely to need this space again soon, I don't recommend shrinking.  

    I recommend trying this in a test environment to ensure you have the process down, and to get a feel for the length of time it will take, and the amount of transaction log activity that will be  generated.

    • Proposed as answer by Alex Feng (SQL) Tuesday, November 16, 2010 7:38 AM
    • Marked as answer by Gaurav 17 Tuesday, November 16, 2010 5:38 PM
    Tuesday, November 16, 2010 5:38 AM
    Answerer

All replies

  • Hi Gaurav,

    One of the restrictions with deleting from heaps is that pages will not be deallocated unless one of the following is true (see Books Online at http://msdn.microsoft.com/en-us/library/ms189835.aspx, section "Deleting Rows from a Heap"):

    1. Specify the TABLOCK hint in your delete statement.  Note that this will lock the entire table, making it unavailable for a the duration of the delete.

    2. Use TRUNCATE TABLE to delete all rows.  No locking, no transaction logging, almost instantaneous.

    3. Create a clustered index, perform the delete, and drop the clustered index. That's Books Online's idea.  I see no reason why you can't perform the delete, then create and drop the clustered index.

     

    If you do delete rows instead of truncating, you will need space in your transaction log to record 40 million delete operations.  Make sure you have sufficient space for this.  It is worth considering doing the delete in small batches.  This reduces locking, and minimises rollback time.  (If you do the delete in a single transaction and it takes 2 hours and you then cancel it, you can expect a further 2 hours to rolback!)

    After deleting, and having the pages deallocated, the database's data file (MDF) will not have shrunk, but there will be space available.  Shrinking the data file will reclaim this space, but will result in a fragmented database.  If your database is likely to need this space again soon, I don't recommend shrinking.  

    I recommend trying this in a test environment to ensure you have the process down, and to get a feel for the length of time it will take, and the amount of transaction log activity that will be  generated.

    • Proposed as answer by Alex Feng (SQL) Tuesday, November 16, 2010 7:38 AM
    • Marked as answer by Gaurav 17 Tuesday, November 16, 2010 5:38 PM
    Tuesday, November 16, 2010 5:38 AM
    Answerer
  • Thanks Jim for the detailed response...I think you have answered my question.

     


    Thanks, Gaurav
    Tuesday, November 16, 2010 5:38 PM