We Cut a 190 GB Production Database to 45 GB in One Night. Here's What We Learned

A production database that had grown to a massive 190 GB in size was reduced to just 45 GB in one night. The database belonged to an internal application that had accumulated twelve years of project data, causing the file size to inflate. The team aimed to shrink the database before migrating it to a cloud instance where every gigabyte would incur a monthly cost. Here are six key lessons learned from the process:

Lesson 1: The actual database size is often smaller than expected. Initial estimates could be misleading. The team discovered that the database was using only 106.7 GB out of the allocated 190.6 GB, with the remaining space being empty pages that had previously been allocated and not released. This realization changed the scope of the project, focusing on reclaiming used space instead of deleting large amounts of data.

Lesson 2: Database size does not necessarily correlate with the number of rows. Although it was expected that the largest tables would contain the most rows, the biggest space consumers turned out to be specific tables with relatively few rows. For instance, a configuration table with 13 GB and 21,000 rows had unusually large data per row, while an attachment metadata table with 3.7 GB and 8,700 rows was critical for the application's functionality.

This finding showed that focusing on row counts alone would miss potential optimization opportunities.

Lesson 3: Transaction logs can quickly consume available space. During the purge process, the transaction log had nearly filled the available 40 GB, preventing the complete deletion of data. To resolve this, the team set the database recovery mode to "Simple," which allowed the log to truncate regularly and prevent it from growing beyond the available space. This adjustment enabled the deletion of the targeted data without causing the operation to fail due to log space constraints.

Lesson 4: Implementing abort gates and flags is crucial when performing large-scale database deletions. The team designed a purge script with multiple stages, each controlled by verification checks and abort flags. This approach allowed for safe stopping and resuming of the purge process without risking a full rollback of the committed work. It also helped in identifying any unexpected deletions, such as orphaned parent records, ensuring the operation's accuracy and reliability.

Lesson 5: Documenting what was not deleted is as important as the deletion process itself. The team created before/after assertions on essential data records, ensuring that critical information, such as active projects and related records, remained intact. By providing detailed count comparisons before and after the purge, they were able to confidently demonstrate that no important data was lost during the process. This transparency reassured stakeholders and avoided potential disputes over data integrity.