RDS SQL Server空间不足问题

重要

本文中含有需要您注意的重要提示信息,忽略该信息可能对您的业务造成影响,请务必仔细阅读。

RDS SQL Server实例的空间使用率是日常需要重点关注的监控项之一,实例的存储空间不足,会导致严重后果,例如数据库无法写入、数据库无法备份、存储空间扩容任务耗时过长等。本文介绍如何查看空间使用情况以及排查空间问题。

查看空间使用情况

  • 方法一:您可以在实例的基本信息页面查看存储空间使用情况,但此页面只展示当前的空间使用总量,不会展示各类数据分别占用的磁盘空间信息,也没有空间使用的历史信息。基本信息

  • 方法二:您可以在实例的监控与报警页面,在标准监控页面下查看实例各类数据占用的磁盘空间信息和历史变化曲线。各类指标含义,请参见查看标准监控

    image

  • 方法三:您可以在控制台的自治服务 > 空间管理页面,查看更详细的空间使用情况,包括数据与日志的空间使用对比、空间使用的历史变化趋势、Top数据库和Top表的空间分配明细等。更多信息,请参见空间管理

    说明

    不支持RDS SQL Server 2008 R2云盘版实例。

    image

  • 方法四:您也可以使用客户端工具(例如SSMS),查看实例的空间使用信息。相关操作,请参见通过SSMS客户端连接RDS SQL Server实例

    常用的查看SQL Server数据库空间使用信息的命令如下。

    系统视图或命令

    说明

    sp_helpdb

    查看所有数据库各自的总空间大小(数据文件与日志文件大小之和)。

    sp_spaceused

    查看当前数据库的名称、已使用空间大小和未分配空间大小。

    DBCC SQLPERF(LOGSPACE)

    查看所有数据库各自的日志文件总空间大小以及实际已使用日志空间大小。

    DBCC SHOWFILESTATS

    查看当前数据库的所有数据文件空间大小以及实际已使用数据空间大小。

    SELECT * FROM sys.master_files

    查看所有数据库各自的数据和日志文件的大小。

    SELECT * FROM sys.dm_db_log_space_usage

    查看当前数据库的日志空间总空间和实际已使用日志空间大小。仅适用于SQL Server 2012及以上版本。

    SELECT * FROM sys.dm_db_file_space_usage

    查看当前数据库的数据文件总空间和实际已使用数据空间大小。仅适用于SQL Server 2012及以上版本。

如果发现实例的空间使用率过高,首先应该在RDS管理控制台监控与报警页面中检查数据、日志、临时文件、其他文件各部分的空间使用情况,确认是哪种文件的空间增长过快,并进一步评估能否采取措施释放空间或避免空间快速增长。详细的分析与解决方案请参见下文。

回收和释放数据空间

分析原因

数据空间的总大小(即所有数据文件大小总和)是由已分配(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企业版实例可以设置参数ONLINEON,然后执行修改操作,基本不会影响正常业务。

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

  • 整理索引碎片

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

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

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

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

    整理索引碎片

    • 重建(Rebuild)操作

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

      重要

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

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

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

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

      image

    • 重组(Reorganize)操作

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

收缩数据文件

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

重要

单次对SQL Server数据库进行大范围收缩可能导致大量事务日志增长和长时间阻塞,建议使用方式一循环小批次进行收缩

  • 方式一:分批次循环收缩数据文件,建议每次收缩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 DatabaseDBCC 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)

常见问题

RDS实例的索引碎片率较高,执行了Rebuild命令重建索引后,RDS控制台中索引使用信息表中对应表的碎片率为什么没有变化?

RDS控制台显示

image

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

image

执行SHRINKFILE命令后,为什么长时间无法完成且进度百分比一直不更新?

问题描述

在阿里云RDS SQL Server实例中,用户尝试对数据库数据文件或日志文件执行SHRINKFILE操作以回收空闲空间时,可能会遇到以下问题:

  • SHRINKFILE命令长时间未完成。

  • 进度百分比(percent_complete)长时间不更新。

此类问题通常由长事务阻塞引发,特别是在数据库启用了快照隔离(Snapshot Isolation)的情况下,快照版本的保留会阻止SHRINKFILE操作的正常完成。

解决方案

  1. 通过SSMS连接RDS SQL Server实例

  2. 执行以下查询,确认SHRINKFILE操作的状态和进度:

    SELECT 
        r.session_id AS [SPID],
        r.start_time AS [开始时间],
        r.status AS [状态],
        r.command AS [命令类型],
        r.wait_type AS [等待类型],
        r.wait_time AS [等待时间(ms)],
        r.last_wait_type AS [上次等待类型],
        t.text AS [执行语句],
        r.percent_complete AS [执行进度]
    FROM 
        sys.dm_exec_requests r
    CROSS APPLY 
        sys.dm_exec_sql_text(r.sql_handle) t;

    如果status列显示为suspended,且percent_complete列的值长时间未更新,则表明SHRINKFILE被阻塞,则执行下一步检查。

    image

  3. 查看RDS SQL Server错误日志,寻找类似如下的日志信息:

    DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
    transaction with timestamp 15 and other snapshot transactions linked to
    timestamp 15 or with timestamps older than 109 to finish.

    示例如下:

    此日志表示数据库开启了快照,SHRINKFILE操作被快照事务阻塞,无法继续执行。

    image

  4. 执行以下查询,检查数据库快照开启情况:

    SELECT 
        name,
        is_read_committed_snapshot_on,
        snaphost_isolation_state,
        snapshot_isolation_state_desc
    FROM 
        sys.databases;

    如果is_read_committed_snapshot_on=1snapshot_isolation_state_desc=ON,则表示数据库启用了快照能力,需进一步排查长事务。

    image

  5. 执行如下SQL,检查可能导致阻塞的长事务,以及长事务的持续时间:

    SELECT db_name(exe.database_id),tr.* FROM sys.dm_tran_active_snapshot_database_transcations AS tr JOIN sys.dm_exec_requests AS exe ON tr.session_id=exe.session_id;

    执行完成后,需重点关注session_idelapsed_time_seconds字段。其中,elapsed_time_seconds表示事务的持续时间,该值越大表明事务运行时间越长,越需要引起关注。如果发现某些长事务可能是导致阻塞的原因,可以考虑参考下一步终止(KILL)这些事务,并观察SHRINKFILE操作是否得以恢复。

    说明

    SHRINKFILE操作被阻塞的原因并不一定局限于当前操作的数据库存在长事务,其他启用了快照的数据库中的长事务也可能成为阻塞的根源,尤其是在涉及跨库查询的场景下。因此,在排查时需全面检查所有相关数据库的事务状态,以确保问题得到准确定位和解决。

    image

  6. 如果通过上述步骤确认某个或某些长事务是导致SHRINKFILE操作被阻塞的原因,则需评估是否可以通过终止(KILL)这些长事务来恢复SHRINKFILE的正常执行。需注意,终止长事务会导致其对应的事务操作被回滚,因此在执行KILL操作前,请充分评估回滚对业务的影响。如果因业务原因无法终止长事务,请参考如下建议:

    • 等待长事务自然结束:在长事务完成后,再次尝试执行SHRINKFILE操作。

    • 终止SHRINKFILE操作:若当前窗口时间不适合继续等待,可选择终止SHRINKFILE操作,并重新规划一个合适的维护窗口时间再执行。

回收日志空间

查看已使用日志空间

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

说明

log_reuse_waitlog_reuse_wait_desc的取值说明,请参见sys.databases (Transact-SQL)

收缩事务日志

警告

如果您的数据库服务器提示“事务日志已满”,此时无法通过控制台收缩事务日志,您可以手动执行SQL语句进行处理,但该方案有使用风险,具体请参见日志空间不足时的解决方案(仅适用于紧急状态时进行)日志空间不足时,一般建议您先扩容磁盘

特性

方案一:单个数据库收缩(仅收缩不备份)

方案二:实例级备份并收缩(先备份再收缩)

操作范围

单个数据库

整个实例

是否备份

不备份

自动备份所有事务日志

空间回收速度

快速

较慢(需先备份再收缩)

适用场景

日志增长快、无法等到下次实例级全量或增量备份(急需空间回收)

日志空间充足(收缩事务日志会占用部分日志空间)、需全局优化

对其他数据库的影响

无影响

影响整个实例

操作方法

收缩数据库事务日志

备份并收缩事务日志

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

image

回收临时文件空间

分析原因

临时文件空间是指系统数据库tempdb占用的空间大小。由于tempdb库总是使用简单恢复模式,因此tempdb库的日志文件通常很小,但是数据文件容易快速增长,例如创建大量临时表、连接大表或排序等都可能导致tempdb库数据文件空间增加。

解决方案

  • 尽量从数据库应用层面规避,例如减少不必要的临时表、大表连接查询、避免大事务等。

  • 在业务低峰期重启RDS实例,重启后tempdb库占用的空间会恢复到实例创建时的大小。

回收其他文件空间

分析原因

其他文件空间在此指sqlserver.other_sizemastersizemodelsizemsdbsize等占用的空间大小。这些文件通常都很小,但是某些情况下占用的空间会很大,例如:

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

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

解决方案

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

    image

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

    说明

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

扩容存储空间

如果RDS实例空间使用率过高,且参考上文后无法有效降低空间使用率,建议及时扩容实例存储空间。具体操作,请参见变更配置