2

I want to find total used and remaining space for each database in Azure SQL MI.

For that, from an example, when I right click on a database and select properties I see following example output, where total size should be ~365 GB:

enter image description here

                         data_size       log_size         total_size
TEST_DB                  355.69042968750    1.31347656250   357.00390625000

When I run the following script Get size of all tables in database sum of Tables are around ~500MB, where I have no idea where is the remaining of 364.5 GB go.

Also instead when I run following solution's script (https://dba.stackexchange.com/a/339009/289736) again I see data sizes as much less from the data size which is around ~765MB:

enter image description here

I get lost correct way to get size of a database, since different approaches show different sizes. If there is a huge gap between allocated size and used size, where does gap generated from?

Improve this question
2
  • 1
    Your screenshot clearly shows you have 363Gb available out of 365Gb so the data pages are only 2Gb the rest is empty. That squares with the results from the other query showing 764Mb of data plus the 1.3Gb log file. You should be able to shrink the DB down if you wish.
    – Charlieface
    Commented Apr 26 at 11:51
  • 2
    To counter @Charlieface, given that this is an Azure SQL Managed Instance, the size of the database files directly correlates to IOPS. Smaller databases have fewer IOPS. I have actually grown my database files so that I can get adequate disk performance. So if you do shrink the database, take care to watch for performance issues. See here for a TSQL script (not mine) I use to measure this github.com/dimitri-furman/managed-instance/blob/master/…
    – Jonathan Fite
    Commented Apr 26 at 14:14

1 Answer 1

Reset to default
3

Remember that database's data and log files are kind of containers with pre-allocated disk space, and if your data file is 365 GB and data inside it is 730 MB then it means the data file is over 99% empty.

I want to find total used and remaining space for each database in Azure SQL MI.

For this particular query, use my ServerSpaceUsage stored procedure.

To know details about a specific database (like yours 365 GB one) and tables inside it, use my DatabaseSpaceUsage

Install DatabaseSpaceUsage to your database in question and run without any parameters, alternatively install to master database and run exec DatabaseSpaceUsage 'TEST_DB'

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.