Reduce Azure SQL Database Size

Question

I'm doing my best an deleting rows and rebuilding indexes but the size of the database is growing really fast and I can't see the effect of my operations.

Is there a cause for this and is there a way to reduce the size of the database other than row deletion and index rebuild?

Thank you very much

Alberto Morillo Alberto Morillo · Accepted Answer · 2017-08-23T03:16:45

Please run the following special store procedure and let us know which database file is getting bigger.

sp_helpfile

If the log file is getting bigger, please run below statement to recover space log.

DBCC SHRINKFILE (log, 0)

If the data file, and not the log file, is increasing in size use below query to know which tables are consuming the most space, and start to investigate from there.

select 
    o.name, 
    max(s.row_count) AS 'Rows',
    sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as 'GB',
    (8 * 1024 * sum(s.reserved_page_count)) / (max(s.row_count)) as 'Bytes/Row'
from sys.dm_db_partition_stats s, sys.objects o
where o.object_id = s.object_id
group by o.name
having max(s.row_count) > 0
order by GB desc

Below query gives you the size of every index also.

select  
    o.Name,
    i.Name,
    max(s.row_count) AS 'Rows',
    sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as 'GB',
    (8 * 1024* sum(s.reserved_page_count)) / max(s.row_count) as 'Bytes/Row'
from 
    sys.dm_db_partition_stats s, 
    sys.indexes i, 
    sys.objects o
where 
    s.object_id = i.object_id
    and s.index_id = i.index_id
    and s.index_id >0
    and i.object_id = o.object_id
group by i.Name, o.Name
having SUM(s.row_count) > 0
order by GB desc

Reduce Azure SQL Database Size

3 Answers