减少Azure SQL数据库大小 [英] Reduce Azure SQL Database Size

问题描述

我正在尽力删除行并重建索引，但是数据库的大小却在迅速增长，我看不到操作的效果.

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?

非常感谢您

推荐答案

请运行以下特殊存储过程，并让我们知道哪个数据库文件越来越大.

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

这篇关于减少Azure SQL数据库大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！