Azure SQL Database :: SHRINKDATABASE increase the unallocated space

Question

I'm trying to squeeze as much Azure SQL Databases as possible in order to make them fir inside a SQL Elastic Pool.

I'm using this query that allows me to compare the unallocated space before and after the operation:

EXEC sp_spaceused @oneresultset = 1
GO

DECLARE @dbName VARCHAR(50)
SELECT @dbName = DB_NAME()
DBCC SHRINKDATABASE(@dbName, TRUNCATEONLY) 
GO

EXEC sp_spaceused @oneresultset = 1
GO

I noticed that sometimes the unallocated space is actually increasing instead of decreasing:

This happened more than once. I read online that after the use of SHRINKDATABASE the indexes might be all over the place but here it seems that the index_size hasn't really changed. And the unused that is next to it it's just 50MB so that cannot be the problem:

How is this possible?

And how to properly SHRINKDATABASE on Azure SQL Database?

You shouldn't shrink databases is not a valid reply because in this scenario, yes, I have to gain space.

Answer 1

There is no point ot reinvent the wheel. Microsoft propose an out-of-the-box solution for Azure SQL Database: Auto-shrink

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

This made the trick.

If you then want to rebuild the indexes, once again, there is no point to reinvent the wheel. Microsoft propose Rebuilding SQL Database indexes using Azure Automation