My Azure SQL Database says it is using 25Gb but the actual data is less than 10Gb

Question

My database in Azure is currently giving me this info:

But the actual data is around 7.4GB (as per the bacpac file size).
Anyway, the size of the LOG seems huge compared to the actual data.
I have the same problem in many other databases in the same Elastic Pool.

What should I do to correctly shrink the databases, right now I'm constantly increasing the size of the Elastic Pool and that is not economically maintainable, I used to be able to shrink everything just fine when I had my DBs in a SQL Server mnaged on-prem, but here on Azure shrinking seems to not take any actual effect?

Thanks!

Answer 1

You are making an apples-to-oranges comparison. A BACPAC is compressed, but the database is not.

I tested making a BACPAC of a database with this output for sp_spaceused:

And here is the BACPAC:

A lot smaller.

Here is a query to list the size of your tables:

; WITH CTE AS (
   SELECT s.name AS schema_name, o.name AS object_name, p.rows,
          (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = o.object_id) AS colcnt,
          CASE WHEN au.type_desc <> 'LOB_DATA' THEN au.total_pages ELSE 0 END * 8192 / 1000000 AS regular_MB, 
          CASE WHEN au.type_desc = 'LOB_DATA' THEN  au.total_pages ELSE 0 END * 8192 / 1000000 AS LOB_MB,
          CASE WHEN au.type_desc <> 'LOB_DATA' THEN au.used_pages  ELSE 0 END * 8192 / 1000000 AS used_regular_MB, 
          CASE WHEN au.type_desc = 'LOB_DATA' THEN au.used_pages   ELSE 0 END * 8192 / 1000000 AS used_LOB_MB
   FROM   sys.schemas s
   JOIN   sys.objects o ON s.schema_id = o.schema_id
   JOIN   sys.indexes i ON o.object_id = i.object_id
   JOIN   sys.partitions p ON i.object_id = p.object_id
                           AND i.index_id = p.index_id
   JOIN   sys.allocation_units au ON au.container_id = 
                                          CASE WHEN au.type IN (1, 3) THEN p.hobt_id
                                               WHEN au.type = 2 THEN p.partition_id
                                          END
   WHERE i.index_id IN (0, 1)
)
SELECT schema_name, object_name, MAX(colcnt) AS no_of_columns, MAX(rows) AS no_of_rows, 
       SUM(regular_MB + LOB_MB) AS total_MB, SUM(used_regular_MB + used_LOB_MB) AS used_MB,
       SUM(regular_MB) AS regular_MB, SUM(LOB_MB) AS LOB_MB, SUM(SUM(regular_MB)) OVER ()
FROM   CTE 
GROUP  BY schema_name, object_name
ORDER BY total_MB DESC, no_of_rows DESC

Answer 2

Hey **Hazael Mojica
**
It sounds like you're experiencing issues with your Azure SQL Database, particularly with the discrepancy between allocated space and actual data size. Here are a few things you can try to help address the issue:

1. Check Index Fragmentation: Sometimes, fragmented indexes can lead to higher-than-expected space usage. Use the following query to evaluate fragmentation:

      SELECT
       OBJECT_NAME(IX.object_id) AS Table_Name,
       IX.name AS Index_Name,
       PS.avg_fragmentation_in_percent
   

FROM sys.indexes AS IX INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS PS ON IX.object_id = PS.object_id AND IX.index_id = PS.index_id WHERE PS.avg_fragmentation_in_percent > 30; -- Adjust threshold as needed

   
1. **Rebuild or Reorganize Indexes**: If you identify fragmented indexes, consider rebuilding or reorganizing them—this can help reclaim space.

1. **Shrink Database**: You can manually shrink the database to reclaim unused space. Use the following commands:

   ```sql
   DBCC SHRINKDATABASE (YourDatabaseName);

Or if you’d like to target a specific file:

   DBCC SHRINKFILE (YourFileName, target_size_in_MB);

Remember, shrinking a database can lead to performance degradation, so it's best to do this during non-business hours.

Clear Ghost Records: These are records marked for deletion but not physically removed. Execute a full index rebuild to clean them up.

1. Manage PVS Size: If using accelerated database recovery, check the Persistent Version Store for excess size. Execute:

      SELECT 
       database_id, 
       persistent_version_store_size_kb 
   

FROM sys.dm_tran_persistent_version_store_stats;

   
   If necessary, use:
   
   ```sql
   EXEC sys.sp_persistent_version_cleanup [YourDatabaseName];

Monitor Allocated Space: Use the following query to check space details, which helps in analyzing further:

   SELECT 
    file_id, type_desc, 
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
    CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
    CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM 
    sys.database_files;

If you’re still facing challenges after trying these suggestions, it might help to know more about the specific configurations of your databases within the Elastic Pool, as well as any recent changes in workload or data activities.

Follow-Up Questions:

1. What service tier are you currently using for your Azure SQL Database?
2. Have you implemented any index maintenance routines previously?
3. Are there any specific times when you notice this issue more prominently?
4. How frequently do you insert or delete data from your databases?

Reference:
https://learn.microsoft.com/azure/azure-sql/database/file-space-manage
https://learn.microsoft.com/en-us/azure/azure-sql/database/performance-tuning-guide
https://learn.microsoft.com/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

Feel free to provide more details, and we can dive deeper into resolving this! Hope this helps!