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.