1. 查看数据文件占用（权限要求较大）
dbcc showfilestats
  1. 查看日志文件占用
 dbcc sqlperf(logspace)
  1. 全面查看：

简易版：

USE master
go
--简易版
SELECT
Name,
physical_name,
Size/128.0 AS [Size(MB)],
FILEPROPERTY(Name,'SpaceUsed')/128.0 AS [SpaceUsed(MB)],
STR(FILEPROPERTY(Name,'SpaceUsed')*1.0/Size*100,6,3) AS [SpaceUsed(%)]
FROM master.sys.database_files

详细版：

SELECT a.name [文件名称]
	,cast(a.[size]*1.0/128 as decimal(12,1)) AS [文件设置大小(MB)]
	,CAST( fileproperty(s.name,'SpaceUsed')/(8*16.0) AS DECIMAL(12,1)) AS [文件所占空间(MB)]
	,CAST( (fileproperty(s.name,'SpaceUsed')/(8*16.0))/(s.size/(8*16.0))*100.0  AS DECIMAL(12,1)) AS [所占空间率%]
	,CASE WHEN A.growth =0 THEN '文件大小固定，不会增长' ELSE '文件将自动增长' end [增长模式]
	,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN '增量为固定大小'
		WHEN A.growth > 0 AND is_percent_growth = 1 THEN '增量将用整数百分比表示'
		ELSE '文件大小固定，不会增长' END AS [增量模式]
	,CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast(cast(a.growth*1.0/128as decimal(12,0)) AS VARCHAR)+'MB'
		WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast(cast(a.growth AS decimal(12,0)) AS VARCHAR)+'%'
		ELSE '文件大小固定，不会增长' end AS [增长值(%或MB)]
	,a.physical_name AS [文件所在目录]
	,a.type_desc AS [文件类型]
FROM sys.database_files  a
INNER JOIN sys.sysfiles AS s ON a.[file_id]=s.fileid
LEFT JOIN sys.dm_db_file_space_usage b ON a.[file_id]=b.[file_id]
ORDER BY a.[type]

SQL Server基于T-SQL 查看所有表大小，所占空间：

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 0
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    总共占用空间 desc

查看索引大小

–查看索引大小
如果您想要表的每个索引的大小，请使用以下两个查询中的一个：
–第1种方法

SELECT
    i.name                  AS IndexName,
    SUM(s.used_page_count) * 8   AS IndexSizeKB
FROM sys.dm_db_partition_stats  AS s
JOIN sys.indexes                AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name

–第2种方法

SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

结果通常略有不同，但在1%之内。
查看堆表及数据量超过10W行的

select * from (
SELECT tables.NAME,
       (SELECT rows
        FROM   sys.partitions
        WHERE  object_id = tables.object_id
               AND index_id = 0 -- 0 is for heap
               -- 1 is for clustered index
                And rows >=100000
       )AS numberofrows
FROM   db_tank.sys.tables tables
WHERE  Objectproperty(tables.object_id, N'TableHasClustIndex') = 0
)t where numberofrows is not null

查看表数据行数

但这种办法不是实时的，是sql server定时做的统计操作，执行下面代码可进一步精确

DBCC UpdateUSAGE(DatabaseName,[TABLENAME])WITH ROW_COUNTS

SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC

最简单的办法就是使用SSM客户端、报表查看
–查询数据文件的空间情况

dbcc showfilestats;

–查询日志文件的空间情况

dbcc sqlperf(logspace);

–查询tempdb的空间情况

select * from sys.dm_db_file_space_usage;

–查询表的占用空间情况 ;

exec sys_viewTableSpace --请慎用，对性能有影响

–dbcc showconfig() ;

–日志诊断方法

select name,recovery_model_desc, log_reuse_wait,log_reuse_wait_desc from sys.databases where name = ‘db_name’;

SQL脚本
以下是一些常用的查看SQL Server数据库空间使用信息的系统视图或命令：

sp_helpdb查看所有数据库各自的总空间大小（数据文件与日志文件大小之和）
––
DBCC SQLPERF(LOGSPACE)查看所有数据库各自日志文件的总空间大小以及实际已使用部分的大小
––
sys.master_files查看所有数据库的所有数据和日志文件各自的大小
––
sys.dm_db_file_space_usage查看当前数据库的所有数据文件各自的总空间大小以及实际已使用部分的大小。注：适用于SQL Server 2012以上版本
––
DBCC SHOWFILESTATS查看当前数据库的所有数据文件各自的总空间大小以及实际已使用部分的大小
––
sp_spaceused查看当前数据库的总空间大小、已使用空间大小；查看当前数据库的数据文件的保留空间、数据空间、索引空间及未使用空间大小；查看指定表的保留空间、数据空间、索引空间及未使用空间大小
––

SSMS UI
SQL Server Management Studio客户端工具中提供了一些基本的查看数据库空间使用信息的报表，也可以帮助用户比较方便的分析单个数据库内的空间分配使用情况。
在这里插入图片描述
在这里插入图片描述
空间回收与释放
当遇到实例的空间使用率过高问题时，应首先从RDS控制台的监控与报警页中检查一下数据、日志、临时文件、系统文件各部分的空间使用情况，看下是哪部分的空间使用占比较高或增长速度较快，并进一步评估能否采取措施释放出部分空间或避免空间的快速增长。
数据空间
对于数据空间占比较高的情况，可首先通过CloudDBA空间管理页或sys.master_files等系统视图检查是哪些数据库的数据空间较大，其中CloudDBA中还可以查看空间使用变化的历史曲线，使用起来更为方便。
对于每个数据库来说，其数据空间的总大小（即其所有数据文件大小的总和）是由已分配的（Allocated）和未分配的（Unallocated）两部分组成的。其中未分配的部分是由完全未分配的Extent（每个为连续的64KB空间）构成的，不和任何数据库对象关联，也只有这部分空间有可能通过文件收缩的方式释放出来给操作系统。而已分配的部分通常都是和特定的数据库对象关联的，其中又包括已使用的（Used）和未使用的（Unused）部分，未使用的部分通常只能分配给同一表或索引新增的记录使用，而无法被其他不同的对象直接使用。
在数据库中的数据量持续增长的情况下，数据文件中的未分配部分通常都是很小的，这也就意味着在未对数据库空间的使用主动进行优化之前，直接尝试收缩数据文件的大小一般不会有什么效果。大部分情况下，若需要控制数据空间的增长并进一步降低其大小的话，都应首先对已分配部分的空间使用进行优化和回收，然后再考虑进行数据文件大小的收缩。
数据空间的回收
数据空间的回收通常主要有如下几种方式：
数据归档
将数据库表中不常用的数据（例如早期的历史数据）从当前表中清除掉，并可根据需要转移到其他数据库实例中，或以其他形式进行归档保存，通过直接减少数据量来降低当前数据库对空间使用的需求。
这种方式如果可行的话，往往是控制数据空间增长量的最有效手段，但它通常对于数据库对象结构及相关应用逻辑的设计也有一定的要求，需要应用设计和开发人员的参与配合。
数据压缩
SQL Server企业版及2016以上的所有版本中提供了内置的数据压缩的功能，用户可以在单个表、索引或其分区上开启压缩功能，并且有行压缩和页压缩两种选项。具体功能说明和使用方法可参见：
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
数据的压缩比和表结构、列的数据类型及数值分布情况等都有很大的关系，从百分之几到90%以上都有可能。SQL Server中提供了一个专用的存储过程sp_estimate_data_compression_savings可以帮助快速评估在指定的表或索引上开启行压缩或页压缩可以节省多大的存储空间：
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql
修改表或索引上的压缩选项设置是一个DDL操作，如果是在SQL Server企业版中执行，可以使用ONLINE=ON参数在线的执行，基本不会影响表上的DML操作的正常执行。而如果是在SQL Server标准版或Web版中，由于不支持ONLINE DDL，对于大表执行此类操作将会造成长时间锁表的情况，并可能对业务中的正常数据库访问造成较大影响，通常应放在维护窗口内执行。
通过在表上开启数据或索引压缩来节省存储空间的方式对数据库应用来说是完全透明的，并且还可以提升数据缓存的效率和降低内存压力，但同时往往也会增加数据存取操作过程中的CPU开销，因此其对数据库整体性能的影响是需要根据具体环境具体评估的。如果实例的CPU资源方面没有瓶颈，而存储空间和缓存压力较大的话，则在主要的大表上启用数据压缩往往是一个比较好的优化选项。
索引碎片整理
当表中的索引的碎片率较高时，除了会降低索引扫描等操作的执行效率，往往还会导致实际占用的存储空间更大。从回收数据空间的角度考虑，对表上的索引执行碎片整理的操作也是一个可行的手段。

SSMS工具中自带的Index Physical Statistics报表提供了类似的功能：

在这里插入图片描述

在这里插入图片描述
不过需要注意的是，这里的索引碎片率统计的是逻辑上相邻的索引页在物理位置上不一致的情况的比例，和索引页中的空闲空间比例并不是一个概念，只不过碎片率较高的索引往往也有较大的机会是可回收空间比例较大的索引。如果要分析某个索引的页内平均空闲空间比例的话，可以查询系统视图sys.dm_db_index_physical_stats并使用SAMPLED或DETAILED模式，然后参考结果集中avg_page_space_used_in_percent列的值。同时还要注意的是使用SAMPLED或DETAILED模式查询视图sys.dm_db_index_physical_stats时，会产生大量的索引页读取，并可能对数据库性能造成一定影响，应谨慎操作：
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-physical-stats-transact-sql
对于索引碎片的整理，SQL Server中提供了索引重建（Rebuild）和索引重组（Reorganize）两种操作方式。其中Rebuild的方式往往优化效果更好，对于碎片率高的情况执行效率更高，默认情况下执行过程中会锁表，但在企业版中可以启用Online的模式避免长时间锁表的影响。而Reorganize的操作总是以Online的方式进行，在碎片率较低的情况下执行效率会好一些，但碎片整理的优化效果相对不如Rebuild。
关于碎片整理的更多详细说明，可以参考如下文档：
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes
最后，与通过数据压缩进行空间回收的方式不同，虽然通过索引碎片整理往往页可以从数据文件内回收出来一些存储空间，但这个空间使用的减少可能只是临时性的，如果表上仍一直有较频繁的插入和更新操作，那么在清理完索引碎片后，索引碎片率及索引页内的空闲空间比例很可能会快速上升，并导致索引整体存储空间占用的快速增长。因此通过索引碎片整理来控制数据空间增长的方法，主要还是适用于那些很少再更新的归档数据表，或者作为一种临时的空间压力缓解手段。另外，在对大表执行索引重建或重组的过程中，往往会产生大量的事务日志的写入，这期间也要特别关注日志文件大小增长的情况。在RDS SQL Server实例上开启了每30分钟日志备份选项的情况下，通常是可以比较及时的通过日志备份来回收和复用日志文件中的空间的。
数据文件的收缩
从避免实例的数据空间大小持续过快增长的角度考虑，通过上述方式达成了数据空间回收的效果通常就可以了，因为回收出来的空间就可以继续提供给新的数据空间分配需求使用了，而不会造成数据文件大小的持续增长。
但如果确有必要的话，也可以在数据库中执行DBCC SHRINKFILE命令来对数据文件进行收缩，从而将数据文件中未分配的空闲部分空间释放给操作系统，减少整个实例实际占用的磁盘空间大小。
使用DBCC SHRINKFILE命令来对指定的数据文件进行收缩之前，可以先使用DBCC SHOWFILESTATS命令查看各数据文件的大小及其中实际已分配使用部分的大小，已使用的部分是无法被收缩掉的。
在这里插入图片描述

以上图中的执行结果为例，一个Extent的大小为64KB，因此ID为1的数据文件的总空间大小为104584MB，其中已使用的部分大小为82089MB，则该数据文件通过收缩操作可以缩到的最小大小不会低于82089MB。
若要将该数据文件的大小缩小到90000MB，则可以执行如下命令：
DBCC SHRINKFILE(1, 90000)
关于文件收缩操作的更多详细说明，可以参考如下文档：
https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql
另外要注意的是，数据文件的收缩是一个资源开销较高的操作，会在数据文件上产生大量的磁盘IO，并且还会产生大量的事务日志写入，因此一般应尽量放在实例的负载较低的时候执行。

日志空间
日志空间的回收相对比较简单，首先可以使用DBCC SQLPERF(LOGSPACE)命令或CloudDBA空间管理页查看是哪些数据库的日志文件较大，以及这些库的日志文件中实际已使用部分的比例。
在这里插入图片描述
例如，以上数据库db02的日志文件大小为2312MB，其中已使用的部分比例为99%，这时如果对该数据库的日志文件进行收缩，则几乎不会有任何效果。
这种情况下可首先查询系统视图sys.databases，通过其中log_reuse_wait/log_reuse_wait_desc列的输出信息来判断是什么原因导致事务日志文件中的空间无法被回收：

select name, log_reuse_wait, log_reuse_wait_desc from sys.databases

在这里插入图片描述

关于log_reuse_wait/log_reuse_wait_desc列的各种取值含义的具体说明详见如下文档：
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql
其中最常见的一种情况就是LOG_BACKUP。在RDS SQL Server中，数据库默认都是用的完整恢复模式，这种情况下日志文件中记录的内容不会被自动截断（即使事务已提交），而只有完成日志备份的部分才可以被截断和回收复用。另外由于SQL Server日志文件循环写入的结构特点，最多会需要完成两次日志备份之后，已截断部分的日志文件空间才可以被收缩掉。

由于事务日志的收缩需要先等待日志备份操作的完成，因此如果执行该操作时数据库中还有大量未备份过的日志，则日志文件的收缩仍需要等待较久的时间才能完成。
临时文件空间
临时文件空间指的是SQL Server中的系统数据库tempdb占用的空间大小。由于tempdb库总是使用简单恢复模式，因此其日志文件增长到很大的情况是很少见的。但tempdb库的数据文件增长到很大的情况则较为常见，如大量临时表的使用、大表连接或排序操作、大量基于快照的row versioning数据等都可能导致tempdb库数据文件空间的大量使用。
查看数据库空间使用情况的命令如DBCC SHOWFILESTATS等对于tempdb库也是适用的。但是在很多情况下，即使在tempdb库中显示有大量未使用的空间，通过DBCC SHRINKFILE命令也无法有效的将其收缩到很小。并且在RDS中默认也没有授予用户直接访问tempdb库的权限，因此用户也无法直接对tempdb库执行文件收缩的操作。
因此针对tempdb库空间占用过大的情况，一方面可尽量从数据库应用层面规避，如减少不必要的临时表的使用、减少不必要的大表连接查询、避免数据库中有长事务等；另一方面就是当tempdb库增长到较大时，可以安排合适的时间对RDS实例执行一次重启操作，SQL Server服务重启之后，tempdb库会恢复到实例创建之初时的大小。
关于SQL Server中的tempdb数据库空间使用情况的监控与分析，可以参考如下链接：
https://www.mssqltips.com/sqlservertip/4356/track-sql-server-tempdb-space-usage/
系统文件空间
系统文件空间包括系统数据库master、msdb和model的所有文件，以及SQL Server实例系统目录下的一些文件，如错误日志、default trace、系统扩展事件文件等。
通常情况下，以上这些文件都不会很大，一般加在一起也不会超过几百MB。但有些情况下，SQL Server的错误日志文件大小增长到几GB甚至更大也是有可能的。
例外一种可能导致系统文件空间增长到很大的情况，就是SQL Server服务在运行过程中遇到了严重的异常时自动产生的内存转储（memory dump）文件，这通常意味着可能遇到了SQL Server产品中的bug。虽然这种问题发生的几率不高，但如果出现是有可能导致短时间内生成较多的dump文件并占用较大的磁盘空间的。
对于RDS用户来说，无法直接获知各类系统文件实际占用的磁盘空间的大小。如果在监控中发现系统文件空间使用过高的情况，可提交工单联系阿里云售后帮助检查具体的原因。
存储空间扩容
当出现RDS实例存储空间使用率过高，且参考上述说明评估后发现无法有效降低空间使用大小的情况下，应及时对实例进行存储空间的扩容。
各版本的RDS SQL Server实例在执行存储空间扩容时的操作方式都是一样的，即直接从RDS控制台上发起“变更配置”的操作，并指定扩容后存储空间大小及切换时间规则：
在这里插入图片描述

但是由于不同版本的RDS SQL Server实例在底层架构上的差异，其存储空间扩容任务执行的机制及变配所需时间也有所不同。

  1. RDS SQL Server 2008 R2本地盘版高可用版
    由于采用多实例共享同一物理主机上的本地磁盘的模式，在做存储空间扩容时，如果本地磁盘可用空间充足，则任务可在1分钟以内完成，并且不会造成任何数据库访问的中断。
    但如果本地磁盘空间不能满足扩容要求的话，则会产生跨机迁移的任务，根据实例数据量大小的不同，整个任务的执行用时在20分钟到1-2天不等，其中切换时的数据库访问中断时间一般不超过30秒。
  2. RDS SQL Server 2008 R2及以上云盘版高可用版
    对于云盘版实例来说，无法直接在线的完成存储空间的扩容，但是也无需在实例或主机之间进行数据的迁移。存储空间扩容任务的执行过程，是首先在备实例上将操作系统关机并进行云盘空间的快速扩容，然后启动备实例，待主备数据同步之后进行主备切换，最后在新的备实例（即原主实例）上重复同样的过程。
    上述过程中的整体用时一般在10-20分钟之间，其中切换时的数据库访问中断时间一般不超过30秒。
  3. RDS SQL Server 2012及以上云盘版单机版
    对于单机版实例来说，为进一步确保存储空间扩容过程中的数据可靠性，在正式开始变配任务执行前会先对实例上的数据库做一次临时备份（通常为增量备份），之后是将实例的操作系统关机并进行云盘空间的快速扩容，最后启动实例并等待数据恢复完成。
    视具体情况的不同，正式开始变配操作前等待临时备份完成的时间从几分钟到几小时不等，变配过程中数据库访问中断的时间一般在几分钟到十几分钟之间。
# 数据库
Logo
GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

cover

GitTalk | 使用面向业务的狮偶编程语言提升开发效率

avatar GitCode 开源社区
cover

GitTalk | DevUI Suits 场景解决方案

avatar GitCode 开源社区
cover

GitTalk | DevUI Admin 前端项目构建

avatar GitCode 开源社区