Azure sql database taking empty space and not freeing them

Question

My DB size is 1.2 GB something and it only has two tables with 18 and 16 rows respectively with no indexes.

I used SHRINKDATABASE and the size reduced to 66MB !!!
This is a significant size decrease.
I am trying to understand what might be the reason for this!

Answer 1

@Ayush shrivastava

The SHRINKDATABASE command reclaims unused space and compacts the database files.

Here are a few reasons why you might have seen such a large decrease:

1. Unused Space: Even though your database only has two tables with a small number of rows, there might have been a lot of unused space due to deleted rows or other operations that left space unallocated.
2. Fragmentation: Over time, databases can become fragmented, leading to inefficient use of space. Shrinking the database can help reduce fragmentation and reclaim space.
3. File Space Allocation: Azure SQL Database allocates space for future growth, and this space isn't automatically reclaimed when data is deleted. The SHRINKDATABASE command helps reclaim this allocated but unused space.

It's important to note that shrinking a database should not be a regular maintenance operation,as it can lead to fragmentation and performance issues. I will recommend monitoring your database size and only shrink it when necessary.

See microsoft documentation https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage?view=azuresql-db