RDS SQL Server磁盘空间满问题

分析原因

数据空间的总大小（即所有数据文件大小总和）是由已分配（Allocated）空间和未分配（Unallocated）空间两部分组成：

• 已分配空间包括已使用（Used）空间和未使用（Unused）空间，未使用空间只能分配给同一表或索引新增的记录使用，其他数据库对象无法直接使用。

• 未分配空间是由完全未分配的区（Extent）构成的，每个区是连续的64 KB空间。未分配空间不和任何数据库对象关联，这部分空间可以通过收缩文件的方式释放。

在数据量持续增长的情况下，未分配空间通常很小，所以直接收缩文件效果很差，建议首先对已分配空间进行优化和回收，然后再考虑收缩文件。

回收数据空间

通常数据空间的回收有如下几种方式：

• 归档数据

  删除数据库中不常用的数据（例如早期的历史数据），或者根据需要迁移到其他数据库实例中，或者以其他形式归档保存，通过直接减少数据量来降低已使用数据空间大小。

  这种方式是控制数据空间增长的有效手段，但是对数据库对象结构及相关应用逻辑的设计有一定要求，需要应用设计和开发人员的参与配合。

• 压缩数据

  SQL Server 2016及以上版本实例，或2016以下的企业版实例，内置数据压缩功能，您可以在单个表、索引或分区上开启压缩功能，包括行压缩和页压缩。更多信息，请参见Data Compression。

  数据压缩比由表结构、列的数据类型和数值分布情况等决定，跨度较大，例如从10%到90%。SQL Server中提供了一个专用的存储过程sp_estimate_data_compression_savings可以帮助您快速评估在指定的表或索引上开启压缩可以节省多少数据存储空间。

  说明

  • 修改表或索引上的压缩选项设置是DDL操作，大表执行此类操作会造成长时间锁表，可能影响业务，建议在业务低峰期修改。

  • RDS SQL Server企业版实例可以设置参数ONLINE为ON，然后执行修改操作，基本不会影响正常业务。

  • 数据压缩会增加CPU开销，因此需要根据实际业务情况进行评估，建议只在大表上启用数据压缩。

• 整理索引碎片

  索引碎片率较高会导致实际占用的数据存储空间增大，因此通过整理索引碎片可以有效降低数据空间大小。

  查看索引碎片率：您可以在控制台的自治服务 > 性能优化页面，单击索引使用率页签，可以查看各表的索引碎片率统计结果，并且自治服务会提供索引重建（Rebuild）或重组（Reorganize）建议。

  说明

  • 索引碎片率统计的是逻辑上相邻的索引页在物理位置上不一致的比例，和索引页中的空闲空间比例不是一个概念，只是碎片率较高的索引大概率也是可回收空间比例较大的索引。

  • 如果需要分析某个索引的页内平均空闲空间比例，可以使用SAMPLED或DETAILED模式查询系统视图sys.dm_db_index_physical_stats，然后参考结果集中avg_page_space_used_in_percent列的值。更多信息，请参见sys.dm_db_index_physical_stats (Transact-SQL)。查询过程会读取大量索引页，可能影响数据库性能，请在业务低峰期操作。

  

  • 重建（Rebuild）操作

    优化效果较好，对于碎片率高的情况执行效率更高，默认情况下执行过程中会锁表，企业版中可以设置参数ONLINE为ON避免长时间锁表。

    重要

    对于较大的索引，重建操作可能导致数据库存储空间和日志大小在短期内大幅增加。通常情况下，执行索引重建操作前，RDS实例需要预留至少被重建索引大小2倍以上的可用空间，因此请务必确保实例具备足够的可用存储空间。

    查看RDS实例存储空间：进入RDS实例详情页的基本信息页面，在实例资源区域，查看实例总存储空间大小和已使用存储空间大小。

    ALTER INDEX <IX_YourIndexName> ON <YourTableName> REBUILD WITH (ONLINE = ON);

    命令执行完成后，后台会异步重新采集数据，该过程需要一定时间。您可以单击重新采集按钮手动采集最新数据，并等待数据采集完成后单击导出脚本下载到本地确认索引的回收情况。

    

  • 重组（Reorganize）操作

    对于碎片率较低的情况执行效率更高，但优化效果不如重建。

收缩数据文件

上文数据空间回收操作通常可以有效降低数据空间大小，但如果实例空间仍然有压力，可以通过以下两种方式解决：

• 方式一：分批次循环收缩数据文件，建议每次收缩5 GB。示例如下：

  -- 仅适用于SQL Server 2012及以上版本使用，使用前指定
  DECLARE @dbName NVARCHAR(128) = 'YourDBName'  -- 数据库名称
  DECLARE @fileName NVARCHAR(128)  -- 数据文件名
  DECLARE @targetSize INT = 2000   -- 目标大小（MB）
  DECLARE @shrinkSize INT = 5120   -- 循环收缩时每次收缩的大小（MB），建议每次5GB
  DECLARE @currentSize INT         -- 当前大小
  DECLARE @freeSize INT            -- 空闲空间大小
  DECLARE @usedSize INT            -- 已使用空间大小
  
  DECLARE @sql NVARCHAR(500)       
  DECLARE @waitTime INT = 10      -- 每次收缩后等待时间（秒）
  
  -- 获取数据文件名
  SELECT @fileName = name
  FROM sys.master_files
  WHERE database_id = DB_ID(@dbName)
  AND type_desc = 'ROWS'
  
  
  -- 循环收缩
  WHILE 1 = 1
  BEGIN
      -- 获取当前空间大小、空闲空间大小，已使用空间大小
      DECLARE @sql0 NVARCHAR(MAX) = N'
      USE [' + @dbName + '];
      SELECT
        @currentSize = (SUM(total_page_count) * 1.0 / 128),
        @freesize = (SUM(unallocated_extent_page_count) * 1.0 / 128)
      FROM sys.dm_db_file_space_usage
      WHERE database_id = DB_ID();'
  
      EXEC sp_executesql 
        @sql0,
        N'@currentSize INT OUTPUT, @freesize INT OUTPUT',
        @currentSize OUTPUT,
        @freesize OUTPUT
      
      PRINT '当前大小:' + CAST(@currentSize AS VARCHAR(10)) + 'MB'
      PRINT '空闲大小:' + CAST(@freeSize AS VARCHAR(10)) + 'MB'
  
      set @usedSize=@currentSize - @freeSize
      PRINT '已使用大小:' + CAST(@usedSize AS VARCHAR(10)) + 'MB'
  
      -- 检查目标大小是否小于已使用空间大小
      IF @targetSize <= @usedSize
      BEGIN
          PRINT '设置目标大小过小，请重新指定大小，targetsize不能小于： ' + CAST(@usedSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
  
      
      -- 如果达到目标大小则退出
      IF @currentSize <= @targetSize
      BEGIN
          PRINT '收缩完成，当前大小: ' + CAST(@currentSize AS VARCHAR(20)) + 'MB'
          BREAK
      END
      
      -- 计算本次收缩后的大小
      DECLARE @newSize INT = @currentSize - @shrinkSize
      IF @newSize < @targetSize
          SET @newSize = @targetSize
          
      -- 执行收缩
      SET @sql = 'USE [' + @dbName + '];DBCC SHRINKFILE (N''' + @fileName + ''', ' + CAST(@newSize AS VARCHAR(20)) + ')'
      PRINT '执行收缩: ' + @sql
      EXEC (@sql)
      
      -- 等待一段时间后继续
      PRINT '等待 ' + CAST(@waitTime AS VARCHAR(10)) + ' 秒后继续...'
      WAITFOR DELAY '00:00:10'
  END

• 方式二：执行DBCC SHRINKFILE命令直接收缩单个数据文件，将数据文件中未分配空间释放给操作系统。更多信息，请参见Shrink a Database和DBCC SHRINKFILE (Transact-SQL)。

  DBCC SHRINKFILE(<文件ID>, <期望收缩后的大小（单位MB）>)

  点击展开查看示例

  以上图为例，一个区（Extent）的大小为64 KB，数据文件的总空间大小为（1673344 x 64）/ 1024 = 104584 MB，已分配空间为（1313432 x 64）/ 1024 = 82089.5 MB，即压缩后数据文件空间的总大小不会低于82089.5 MB 。因此要将该数据文件空间的总大小缩小到90000 MB，可执行如下命令：

  DBCC SHRINKFILE(1, 90000)

常见问题

查看已使用日志空间

回收日志空间比较简单，使用DBCC SQLPERF(LOGSPACE)命令或自治服务DAS查看数据库的日志文件空间中实际已使用部分的比例。如果已使用部分的比例较高，收缩日志文件几乎没有效果，可以查询系统视图sys.databases，通过log_reuse_wait和log_reuse_wait_desc列的输出信息判断空间为何无法回收。

收缩事务日志

收缩完成后，您可前往RDS实例详情页监控与报警页面，查看最新的日志空间情况。

分析原因

解决方案

分析原因

其他文件空间在此指sqlserver.other_size、mastersize、modelsize、msdbsize等占用的空间大小。这些文件通常都很小，但是某些情况下占用的空间会很大，例如：

• 错误日志errorlog较多，错误日志文件大小增长到几GB甚至更大。

• 严重异常时自动产生的内存转储（memory dump）文件。

解决方案

1. 您可以在RDS实例详情页监控与报警页面的标准监控页签下，查看这些文件所占用的空间大小。各类指标含义，请参见查看标准监控。

   

2. 若errorlog占用较大，您可在日志管理页面对错误日志进行清理。具体操作，请参见管理日志。

   说明

   若出现其他文件（例如sqlserver.other_size）占用过大的情况，请联系技术支持人员，技术支持人员会协助您锁定原因并处理。