First published on MSDN on Feb 08, 2019
There are multiple ways to achieve this and there are also some
storage types
you should be aware
There is a good ...
Updated Mar 14, 2019
Version 2.0
Blog Post
I just checked. I had modified the query to look like this instead, and this is the one I was eventually using:
SELECT rs2.database_name
, rs2.storage_in_megabytes
, rs2.allocated_storage_in_megabytes
, allocated_storage_in_megabytes - storage_in_megabytes difference
FROM sys.resource_stats rs2
JOIN (
SELECT rs1.database_name
, max(rs1.end_time) as end_time
FROM sys.resource_stats rs1
GROUP BY rs1.database_name) t
ON t.database_name = rs2.database_name
AND t.end_time = rs2.end_time
ORDER BY allocated_storage_in_megabytes - storage_in_megabytes desc, database_name
The reason is that the one I shared just showed storage_in_megabytes for each database. I forgot that I made a change to it. This one shows storage in megabytes, allocated storage in megabytes, and the difference. So I sorted by the difference so that I could see which ones were allocated (a lot) more than was being used. I went through those at the top and ran the shrinkdatabase on them. I was able to do it without concern since the elastic pool in my case was dedicated to testing and QA. I didn't run that on the production server / databases though. They go cheap on us for testing and QA, and we have to drop and shrink all the time to reclaim space.
So, yes, the storage_in_megabytes is the amount consumed, as in data (and maybe log files size?). The allocated_storage_in_megabytes is larger since it accounts for transactions and growth. In my specific case I created a database and started copying data from a production database onto my test database, and got an error since the allocated_storage_in_megabytes pushed it over the top. The whole thing failed, and the database itself resorted to a very small "storage_in_megabytes", however since the copying was adding up "allocated_storage_in_megabytes" the elastic pool had already maxed out.
Now, when the QA guy created a new database as part of his testing, he was unable to. I ran this query and could see that the storage_in_megabytes for the database I added was minimal, but that the allocated_storage_in_megabytes was huge. I shrank that database to solve the problem.
