在 Azure SQL 数据库 中管理数据库的文件空间

适用于：Azure SQL 数据库

本文介绍Azure SQL 数据库中数据库的不同类型的存储空间。 你有时可能需要明确管理分配的文件空间。 本文将介绍实现这一目标的步骤。

概述

某些工作负载模式可能导致分配给数据文件的空间超过已使用空间。 这种情况发生在由于数据增长导致使用空间增加，但你后来删除或压缩数据时。 分配但未使用的空间不会自动回收，因为回收资源消耗大，会减缓未来的文件增长。

在以下情况下，您可能需要缩小数据文件并回收未使用的空间：

• 当弹性池中某些数据库分配空间过大导致库接近最大容量时，以促进弹性池中数据库的数据增长。
• 以降低单个数据库或弹性池的最大容量。
• 将数据库或弹性池改为最大大小限制更低的层级。
• 用于降低使用超大规模服务层时的存储成本。

监视文件空间用量

Azure 资源管理器（ARM）API，包括PowerShell get-metrics，返回数据库和弹性池的已使用和分配空间。

以下系统视图还返回数据库和弹性池的已使用和分配空间大小：

• sys.resource_stats
• sys.elastic_pool_resource_stats
• sys.dm_elastic_pool_resource_stats

了解数据库存储空间的类型

了解以下存储空间数量对于管理数据库的文件空间非常重要。

下图演示了数据库的不同存储空间类型之间的关系。

查询单一数据库的文件空间信息

使用以下查询 sys.database_files，返回已分配的以及已分配但未使用的数据库文件空间量。

-- Connect to a user database
SELECT file_id,
       type_desc,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS DECIMAL (19, 4)) * 8 / 1024. AS space_used_mb,
       CAST (size / 128.0 - CAST (FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS DECIMAL (19, 4)) AS space_unused_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;

了解弹性池存储空间的类型

了解以下存储空间对于管理弹性池的文件空间非常重要。

错误信息“弹性池已达到其存储限制”表明，数据库对象占用了足够的空间，已达到弹性池的最大存储大小限制。 考虑提高存储限制，或释放数据空间，如 “回收未使用分配空间”中所述。

查询弹性池的存储空间信息

请使用以下查询确定弹性池的存储空间量。

已用的弹性池数据空间

请使用以下示例查询返回所使用的弹性池数据空间量。 修改弹性池名称参数，使其与池名匹配。

-- Connect to master
SELECT TOP (1) avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_used_mb,
               avg_allocated_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS elastic_pool_space_allocated_mb,
               elastic_pool_storage_limit_mb AS elastic_pool_maximum_size_mb
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

回收已分配但未使用的空间

收缩数据文件

由于缩小数据文件会影响数据库性能，Azure SQL 数据库 不会自动缩小数据文件。 如果需要，你可以在选择的时间压缩数据文件。 不要把看心理医生变成例行公事。 相反，建议只有在大幅减少空间消耗后才使用。

要收缩文件，请使用 DBCC SHRINKDATABASE 或 DBCC SHRINKFILE T-SQL 命令：

• DBCC SHRINKDATABASE 只需一个命令，就能将数据库中的所有数据和日志文件压缩。 该命令一次收缩一个数据文件，对于较大的数据库，这可能需要很长时间。 它还收缩日志文件，这通常是不必要的，因为Azure SQL 数据库根据需要自动收缩日志文件。
• DBCC SHRINKFILE 命令支持更高级的方案：
  • 它可根据需要以单个文件为目标，而不必收缩数据库中的所有文件。
  • 每个 DBCC SHRINKFILE 命令都可以与其他 DBCC SHRINKFILE 命令并行运行，以减少 shrink 的总耗时，但代价是会占用更多资源，并且更有可能暂时阻塞用户查询和并发执行的 DBCC SHRINKFILE 命令。
  • 如果文件尾部不包含数据，你可以通过指定 TRUNCATEONLY 参数来更快地减少分配的文件大小。 TRUNCATEONLY 它不需要文件内的数据移动，但也不会大幅减少分配的大小。
• 有关这些收缩命令的详细信息，请参阅 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE。

在连接到目标用户数据库（而非数据库） master 时运行以下示例。

若要使用 DBCC SHRINKDATABASE 收缩给定数据库中的所有数据和日志文件，请执行以下命令：

DBCC SHRINKDATABASE (N'database_name');

数据库可能包含一个或多个数据文件，这些文件是随着数据增长自动生成的。 要确定数据库的文件布局，包括每个文件的使用和分配大小，请使用以下示例脚本查询 sys.database_files 目录视图：

-- Review file properties, including the file_id and name values to use in shrink commands
SELECT file_id,
       name,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) * 8 / 1024. AS space_used_mb,
       CAST (size AS BIGINT) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS BIGINT) * 8 / 1024. AS max_file_size_mb
FROM sys.database_files
WHERE type_desc IN ('ROWS', 'LOG');

要缩小单个文件，请使用 DBCC SHRINKFILE 以下命令，例如：

-- Shrink database data file named 'data_0` by removing all unused at the end of the file, if any.
DBCC SHRINKFILE ('data_0', TRUNCATEONLY);

收缩事务日志文件

与数据文件不同，Azure SQL 数据库自动收缩事务日志文件，以避免过多的空间使用，从而导致空间不足错误。 在大多数情况下，无需收缩事务日志文件。

在高级和业务关键服务层，如果交易日志变得庞大，可能会显著增加本地存储的使用，接近 最大本地存储 限制。 如果本地存储占用接近极限，你可以选择像下面示例所示的 DBCC SHRINKFILE 命令缩小事务日志。 此命令完成后，将立即释放本地存储，而无需等待定期自动收缩操作。

在连接到目标用户数据库时运行以下示例，而不是数据库。master

-- Shrink the database log file (always file_id 2), by removing all unused space at the end of the file, if any.
DBCC SHRINKFILE (2, TRUNCATEONLY);

自动收缩

作为手动收缩数据文件的替代方法，可以为数据库启用自动收缩。 但是，与 DBCC SHRINKDATABASE 和 DBCC SHRINKFILE 相比，自动收缩在回收文件空间方面的效率更低。

默认情况下，自动收缩处于禁用状态，这是适用于大多数数据库的建议设置。 如果需要启用自动缩小功能，建议在实现空间管理目标后关闭它，而不是永久启用。 有关详细信息，请参阅 AUTO_SHRINK 注意事项。

例如，如果弹性池包含多个数据库，且这些数据库持续经历显著的扩展和减少，导致池接近最大大小限制，自动缩小功能就很有用。 此方案并不常见。

自动缩小数据库选项在超大规模数据库中没有效果。

要启用自动收缩，请在连接到数据库（而非 master 数据库）时执行以下命令。

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT
    SET AUTO_SHRINK ON;

有关此命令的详细信息，请参阅 DATABASE SET 选项。

收缩后的索引维护

在缩小操作完成后，索引可能会变得碎片化。 对于现代平台上的大多数工作负载，索引碎片不太可能影响性能。 对于使用大索引扫描的工作负载，分片可能会降低读取I/O吞吐量。 如果在缩小操作完成后出现性能下降，建议考虑进行索引维护以重建或重组索引。 索引重建需要数据库中的空闲空间，因此它们可能导致分配空间增加，抵消缩减的影响。

有关索引维护的详细信息，请参阅优化索引维护以提高查询性能并减少资源消耗。

收缩大型数据库

当数据库中分配的空间达到数百吉字节或更多时，缩减可能需要很长时间。 对于多TB数据库，缩减操作可能持续数小时、数天甚至数周。 本节介绍流程优化和最佳实践，使流程更高效且对应用工作负载影响更小。

捕获空间使用情况基线

在开始收缩之前，通过执行以下空间使用情况查询来捕获每个数据库文件中当前已使用和已分配的空间：

SELECT file_id,
       CAST (FILEPROPERTY(name, 'SpaceUsed') AS BIGINT) * 8 / 1024. AS space_used_mb,
       CAST (size AS BIGINT) * 8 / 1024. AS space_allocated_mb,
       CAST (max_size AS BIGINT) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

收缩完成后，可以再次执行此查询，并将结果与初始基线进行比较。

截断数据文件以获得快速但有限的增益

如果你想快速减少分配空间，可以考虑用参数DBCC SHRINKFILE执行TRUNCATEONLY。 如果文件末尾有分配但未使用的空间，操作会快速移除该空间，且不涉及数据移动。

不过，如果你的目标是最大化分配空间的减少，就不要使用 TRUNCATEONLY 。 为了实现这个目标，你需要按照本节后面描述的完整缩小流程进行。 因为该过程会在末尾截断文件，所以单独执行带有 TRUNCATEONLY 的收缩操作没有任何益处。

以下示例命令截断了文件ID 4：

DBCC SHRINKFILE (4, TRUNCATEONLY);

在对每个数据文件执行此命令后，重新运行空间使用查询，看看分配空间的减少情况（如果有的话）。 你也可以在 Azure 门户中查看数据库的分配空间。

评估索引页密度

作为一个可选但推荐的步骤，确定数据库中索引的平均页面密度。 在数据量相同的情况下，如果页面密度较高，缩小操作会更快完成，因为该操作在每个文件中需要移动的页面更少。 如果某些索引的页面密度较低，请考虑对这些索引执行维护，以增加页面密度，然后再收缩数据文件。 更高的页面密度可使 shrink 进一步减少已分配的存储空间。

若要确定数据库中所有索引的页面密度，请使用以下查询。 页面密度在 avg_page_space_used_in_percent 列中报告。

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'SAMPLED') AS ips
     INNER JOIN sys.indexes AS i
         ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
ORDER BY page_count DESC;

如果有些索引页数较多（如列 page_count 所示），其页面密度低于60-70%，建议在缩减数据文件前重建或重组这些索引。

对于较大的数据库，用于确定页面密度的查询可能需要很长时间才能完成。 重新生成或重新组织大型索引也需要大量时间和资源使用。 但是，收缩之前的索引维护可以缩短收缩持续时间，并实现更大的空间节省。

如果有多个索引的页面密度较低，可以在多个数据库会话中并行重新生成它们，以加快该过程。 不过，确保这样做不会超过数据库资源限制。 为可能正在运行的应用程序工作负荷留出足够的资源余地。 在Azure门户或使用sys.dm_db_resource_stats视图中监控资源消耗（CPU、数据输入输出、日志输入输出）。 只有当这些维度的资源利用率都明显低于100%时，才开始额外的索引操作。

示例索引重建命令

以下示例命令使用 ALTER INDEX 语句来重建索引并增加其页面密度：

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (
    FILLFACTOR = 100, MAXDOP = 8, ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
    RESUMABLE = ON
);

此命令将启动联机的可恢复索引重新生成。 此操作允许并发工作负荷在重新生成过程中继续使用表，并允许在因任何原因中断时恢复重新生成。 但是，这种类型的重新生成比脱机重新生成慢，后者会阻止对表的访问。 如果在重新生成期间没有其他工作负载需要访问表，请将 ONLINE 和 RESUMABLE 选项设置为 OFF 并删除 WAIT_AT_LOW_PRIORITY 子句。

若要了解有关索引维护的详细信息，请参阅优化索引维护以提高查询性能并减少资源消耗。

缩小前重组索引

在缩小前重新组织索引可以在两种情况下显著加快缩减操作。

1. 如果数据库符合以下所有条件：

   • 它拥有大量数据文件（超过10个）。
   • 数据库中有大量表（数百个或更多），总共占用大量空间（数百吉字节或更多）。
   • 大量数据会从某些表中删除。

   对于这类数据库，重新组织删除数据表上的索引可以缩短缩小过程中的长期阶段。

2. 如果数据库包含：

   • 大对象（LOB）数据类型，如varchar（max）、nvarchar（max）、varbinary（max）、xml或类似数据类型存储在LOB_DATA分配单元中。
   • 存储在中的ROW_OVERFLOW_DATA。
   • 列存储索引。

   为了让 Shrink 运行更快并释放更多空间，在重新组织索引时一定要包含该 LOB_COMPACTION 子句。 对于包含 LOB 列或大型行的所有索引，建议在缩小之前先进行 LOB 压缩。

   在收缩之前重新组织或重新生成列存储索引可以同样提高收缩速度和有效性。

以下示例展示了重组索引并执行LOB压缩的命令：

ALTER INDEX [index_name] ON [schema_name].[table_name]
REORGANIZE WITH(LOB_COMPACTION = ON);

并行缩减多个数据文件

需要数据移动的缩减操作是一个长期的过程。 如果数据库有多个数据文件，可以通过并行收缩多个数据文件来加快该过程。 打开多个数据库会话，并在每个会话DBCC SHRINKFILE上使用不同的file_id值。 与前面重新生成索引类似，在开始每个新的并行收缩命令之前，请确保你有足够的资源空余空间（CPU、数据 IO、日志 IO）。

以下示例命令将文件ID 4缩小，试图将其分配大小减少到52,000 MB：

DBCC SHRINKFILE (4, 52000);

为了将文件分配的空间减少到最小，执行该语句时不指定目标大小：

DBCC SHRINKFILE (4);

如果你启动过多并行缩小操作，可能会观察到资源利用率高，缩小操作之间存在锁竞用。 在大多数情况下，最优的并行收缩操作数在四到八个范围内。

按增量步骤缩小

如果缩减操作意外停止（例如由于计划内或计划外维护），则工作负载可能会在缩减操作截断文件之前开始使用该操作释放的空间，从而导致缩减操作到目前为止已取得的部分进展丢失。 因为压缩过程通常需要较长时间，发生中断的可能性更高。

为避免此问题，可以逐步压缩每个文件。 在命令中 DBCC SHRINKFILE ，设置目标小于当前文件分配空间，但大于 基线空间使用查询 返回的已使用空间。

例如，如果文件ID 4的空间分配为200,000 MB，而你想将其缩小到100,000 MB，你可以先将目标设置为180,000 MB：

DBCC SHRINKFILE (4, 180000);

此命令将分配大小减少至 180,000 MB 后，您可以再次运行缩小，先将目标设置为 160,000 MB，再设为 140,000 MB，并持续减少目标，直到文件达到所需大小。

分阶段缩减文件可能会花费更长时间，但可以降低因意外中断而不得不重新缩减整个文件的风险。

作为起始值，使用 10 到 20 GB 之间的增量值。 你可以根据你的情况调整增量。 较大的增量可能让你更快完成文件收缩，而较小的增量则可以降低在收缩过程被中断时丢失进度的风险。

监控收缩操作

要监控所有并发运行的收缩会话的收缩进度，请使用以下查询：

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       writes,
       CAST (((DATEDIFF(s, start_time, GETDATE())) / 3600) AS VARCHAR) + ' hour(s), '
               + CAST ((DATEDIFF(s, start_time, GETDATE()) % 3600) / 60 AS VARCHAR) + 'min, '
               + CAST ((DATEDIFF(s, start_time, GETDATE()) % 60) AS VARCHAR) + ' sec'
           AS running_time
FROM sys.dm_exec_requests AS r
     LEFT OUTER JOIN sys.databases AS d
         ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim', 'DbccFilesCompact', 'DbccLOBCompact', 'DBCC');

当所有数据文件的缩小成功完成后，重新运行空间使用查询（或在 Azure 门户中查看），查看分配存储大小的减少情况。 如果已使用空间和已分配空间之间仍然相差很大，请重建或重组索引。 索引重建可能会暂时增加分配空间。 然而，重建索引后再次缩小数据文件通常会导致分配空间的更深层次减少。

收缩期间出现暂时性错误

有时，缩小命令可能会因超时和死锁等错误而失败。 这些错误通常是暂时的，重复同样的指令后不会再发生。 如果收缩操作因出错而失败，仍会保留到目前为止已完成的进度。 再次运行同一收缩命令以继续收缩文件。

当发生瞬态错误时， ShrinkDriver PowerShell 脚本会自动重试 shrink。 用这个脚本缩小大型数据库。

以下示例 T-SQL 脚本展示了如何在重试循环中对单个文件运行缩小程序。 当超时错误或死锁错误发生时，循环会自动在可配置的次数内重试操作。 这种重试方法适用于缩减过程中可能发生的许多其他错误。

DECLARE @RetryCount AS INT = 3; -- adjust to configure desired number of retries
DECLARE @Delay AS CHAR (12);

-- Retry loop
WHILE @RetryCount >= 0
BEGIN
    BEGIN TRY
        DBCC SHRINKFILE (1); -- adjust file_id and other shrink parameters

        -- Exit retry loop on successful execution
        SELECT @RetryCount = -1;

    END TRY
    BEGIN CATCH
        -- Retry for the declared number of times without raising
        -- an error if deadlocked or timed out waiting for a lock
        IF ERROR_NUMBER() IN (1205, 49516) AND @RetryCount > 0
        BEGIN
            SELECT @RetryCount -= 1;

            PRINT CONCAT('Retry at ', SYSUTCDATETIME());

            -- Wait for a random period of time between 1 and 10 seconds before retrying
            SELECT @Delay = '00:00:0' + CAST (CAST (1 + RAND() * 8.999 AS DECIMAL (5, 3)) AS VARCHAR (5));

            WAITFOR DELAY @Delay;

        END
        ELSE -- Raise error and exit loop
        BEGIN
            SELECT @RetryCount = -1;

            THROW;

        END
    END CATCH
END

除了超时和死锁外，shrink 还可能因某些已知问题而出错。

请回顾以下章节中的错误和缓解步骤。

错误编号49503

%.*ls: Page %d:%d could not be moved because it is an off-row persistent version store page. Page holdup reason: %ls. Page holdup timestamp: %I64d.

当长时间运行的活跃事务在持久版本存储（PVS）中生成行版本时，就会发生该错误。 Shrink 无法移动包含行版本的页面。

为缓解此错误，请等待长时间运行的事务完成。 或者，识别并终止长时间运行的事务，但如果你的应用程序无法妥善处理事务失败，此操作可能会对其造成影响。

有关排查可能影响收缩的 PVS 清理延迟的详细信息，请参阅 监视和排查加速数据库恢复。

错误编号5223

%.*ls: Empty page %d:%d could not be deallocated.

该错误可能发生在持续的索引维护操作中，如 ALTER INDEX。 完成这些操作后，重试收缩命令。

如果错误依旧，可能需要重建关联索引。 若要查找要重新生成的索引，请在运行收缩命令的同一数据库中执行以下查询：

SELECT OBJECT_SCHEMA_NAME(pg.object_id) AS schema_name,
       OBJECT_NAME(pg.object_id) AS object_name,
       i.name AS index_name,
       p.partition_number
FROM sys.dm_db_page_info(DB_ID(), <file_id>, <page_id>, default) AS pg
INNER JOIN sys.indexes AS i
ON pg.object_id = i.object_id
   AND
   pg.index_id = i.index_id
INNER JOIN sys.partitions AS p
ON pg.partition_id = p.partition_id;

在执行此查询之前，用错误信息中的实际值替换 <file_id> 和 <page_id> 占位符。 例如，如果消息是： Empty page 1:62669 could not be deallocated，则 <file_id> 是 1 ，且 <page_id> 是 62669。

重新生成由查询标识的索引，然后重试收缩命令。

错误编号5201

DBCC SHRINKDATABASE: File ID %d of database ID %d was skipped because the file does not have enough free space to reclaim.

这个错误意味着数据文件无法再进一步缩小。 可以转到下一个数据文件。

相关内容

• 使用 vCore 购买模型的单一数据库的资源限制
• 使用 DTU 购买模型的单一数据库的资源限制 - Azure SQL 数据库
• 使用 vCore 购买模型为弹性池提供资源限制
• 使用 DTU 购买模型的弹性池的资源限制