Share via

Record deletion order of operations

Cooper, David A. [ISS] (Contractor) 111 Reputation points
2025-12-18T03:13:41.3+00:00

Hi. I need a reminder of order of operations for the following scenario: I have a database of 605 GB in size. The data file is approx 423 GB with 68% available free space. The tran log file is approx 182 GB with 99% available free space. I need to purge roughly 90% of 286 million stale records in a specific table. The table in question has a clustered index and a non-unique/non-clustered index. Primary goal is to delete the table records. Secondary goal is to reduce size of database/log. So, in what order should I perform these operations to accomplish goals most efficiently?: backup tlog file, shrink database/tlog files, delete stale table records, rebuild the indexes, update statistics on the table. Thanks.

SQL Server Database Engine
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Q&A Assist
    2025-12-18T03:13:50.3233333+00:00
    AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more

    To efficiently achieve your goals of deleting stale records and reducing the size of the database and transaction log, follow this order of operations:

    1. Backup the Transaction Log: Start by backing up the transaction log to ensure you can recover to a point in time before the deletion. This also helps in truncating the log, making space available.
    2. Delete Stale Table Records: Proceed to delete the stale records from the table. Since you have a clustered index, this will help maintain the order of the data and optimize the deletion process. Consider using a batch delete approach to minimize locking and logging overhead.
    3. Rebuild the Indexes: After the deletion, rebuild the indexes on the table to optimize performance and reclaim space that may have been fragmented due to the deletions.
    4. Update Statistics: Update the statistics on the table to ensure the query optimizer has the most current information for efficient query execution.
    5. Shrink Database/Transaction Log Files: Finally, shrink the database and transaction log files to reclaim the space. Be cautious with shrinking as it can lead to fragmentation; it’s generally better to leave the space allocated for future growth unless absolutely necessary.

    This sequence ensures that you maintain data integrity and performance while achieving your goals of deletion and space reclamation.

    0 comments
  2. Erland Sommarskog 129.6K Reputation points MVP Volunteer Moderator
    2025-12-18T22:17:50.94+00:00

    Suggested order:

    1. Create a new table with the same schema and indexes, but disable the non-clustered index.
    2. Copy the rows you want to keep to the new table.
    3. Drop the old table.
    4. Rename the new table to have the name of the old.
    5. Shrink database file with a target size that leaves enough free space for your largest table after the purge.
    6. Rebuild all indexes. (This includes the disabled index, which will be re-enabled by this step.)
    7. Shrink the transaction log.

    I'm suggesting to create a new table and copy over, since you are getting rid of most of the current data. Keeping the non-clustered index disabled speeds up the INSERT operation.

    Shrinking a database file introduced fragmentation, hence my suggestion to rebuild indexes.

    Since all these operations may require a bit of log file, it seems best to keep it at the big size until you are done with the rest.

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.