监控SQLServer 数据库表每天的空间变化情况

php中文网

发布时间：2016-06-07 15:44:43

1757人浏览过

来源于php中文网

原创

阅读完桦仔的《分享一个sqlserver脚本（计算数据库中各个表的数据量和每行记录所占用空间）》后，我想使用文中提供的代码做一个统计表每天的新增行数及新增存储空间的功能实现步骤如下： 1. 创建表创建表，存储每天的表空间占用情况 CREATE TABLE [ dbo ]

阅读完桦仔的《分享一个sqlserver脚本（计算数据库中各个表的数据量和每行记录所占用空间）》后，我想使用文中提供的代码做一个统计表每天的新增行数及新增存储空间的功能

实现步骤如下：

1. 创建表

创建表，存储每天的表空间占用情况

<span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>t_rpt_table_spaceinfo</span><span>]</span><span>(
    </span><span>[</span><span>table_name</span><span>]</span> <span>[</span><span>sysname</span><span>]</span> <span>NOT</span> <span>NULL</span><span>,
    </span><span>[</span><span>record_date</span><span>]</span> <span>[</span><span>date</span><span>]</span> <span>NOT</span> <span>NULL</span><span>,
    </span><span>[</span><span>record_time</span><span>]</span> <span>[</span><span>time</span><span>]</span>(<span>7</span>) <span>NOT</span> <span>NULL</span><span>,
    </span><span>[</span><span>rows_count</span><span>]</span> <span>[</span><span>bigint</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>reserved</span><span>]</span> <span>[</span><span>bigint</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>data_size</span><span>]</span> <span>[</span><span>bigint</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>index_size</span><span>]</span> <span>[</span><span>bigint</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>unused</span><span>]</span> <span>[</span><span>bigint</span><span>]</span> <span>NULL</span><span>,
 </span><span>CONSTRAINT</span> <span>[</span><span>PK_t_rpt_table_spaceinfo</span><span>]</span> <span>PRIMARY</span> <span>KEY</span> <span>CLUSTERED</span><span> 
(
    </span><span>[</span><span>table_name</span><span>]</span> <span>ASC</span><span>,
    </span><span>[</span><span>record_date</span><span>]</span> <span>ASC</span><span>,
    </span><span>[</span><span>record_time</span><span>]</span> <span>ASC</span><span>
)
)</span>

2. 新建作业

新建作业，作业计划每天凌晨运行一次，每天记录表占用的空间情况，存储到上一步建立的表中

作业中执行的T-SQL代码为：

PopShort.AI

PopShort是一个AI短剧生成平台

下载

<span>SET</span> NOCOUNT <span>ON</span>  
<span>/*</span><span>创建临时表，存放用户表的空间及数据行数信息</span><span>*/</span>
<span>CREATE</span> <span>TABLE</span><span> #tablespaceinfo
    (
      nameinfo </span><span>VARCHAR</span>(<span>500</span><span>) ,
      rowsinfo </span><span>BIGINT</span><span> ,
      reserved </span><span>VARCHAR</span>(<span>20</span><span>) ,
      datainfo </span><span>VARCHAR</span>(<span>20</span><span>) ,
      index_size </span><span>VARCHAR</span>(<span>20</span><span>) ,
      unused </span><span>VARCHAR</span>(<span>20</span><span>)
    )  
 
</span><span>DECLARE</span> <span>@tablename</span> <span>VARCHAR</span>(<span>255</span><span>);  

</span><span>/*</span><span>使用游标，循环得到表空间使用情况</span><span>*/</span> 
<span>DECLARE</span> Info_cursor <span>CURSOR</span>
<span>FOR</span>
    <span>SELECT</span>  <span>'</span><span>[</span><span>'</span> <span>+</span> <span>[</span><span>name</span><span>]</span> <span>+</span> <span>'</span><span>]</span><span>'</span>
    <span>FROM</span><span>    sys.tables
    </span><span>WHERE</span>   type <span>=</span> <span>'</span><span>U</span><span>'</span><span>;  
 
</span><span>OPEN</span><span> Info_cursor  
</span><span>FETCH</span> <span>NEXT</span> <span>FROM</span> Info_cursor <span>INTO</span> <span>@tablename</span>  
 
<span>WHILE</span> <span>@@FETCH_STATUS</span> <span>=</span> <span>0</span>
    <span>BEGIN</span> 
        <span>INSERT</span>  <span>INTO</span><span> #tablespaceinfo
                </span><span>EXEC</span> sp_spaceused <span>@tablename</span>  
        <span>FETCH</span> <span>NEXT</span> <span>FROM</span><span> Info_cursor  
    </span><span>INTO</span> <span>@tablename</span>  
    <span>END</span> 

<span>INSERT</span> <span>INTO</span><span> t_rpt_table_spaceinfo
(record_date, record_time, </span><span>[</span><span>table_name</span><span>]</span>, <span>[</span><span>rows_count</span><span>]</span><span>
, reserved, </span><span>[</span><span>data_size</span><span>]</span><span>, index_size, unused)
</span><span>SELECT</span> <span>convert</span>(date,<span>getdate</span>()), <span>convert</span>(<span>varchar</span>(<span>8</span>),<span>getdate</span>(),<span>114</span><span>), nameinfo, rowsinfo
,</span><span>CAST</span>(<span>REPLACE</span>(reserved, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) ,<span>CAST</span>(<span>REPLACE</span>(datainfo, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span><span>) 
,</span><span>CAST</span>(<span>REPLACE</span>(index_size, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span>) ,<span>CAST</span>(<span>REPLACE</span>(unused, <span>'</span><span>KB</span><span>'</span>, <span>''</span>) <span>AS</span> <span>BIGINT</span><span>)  
</span><span>FROM</span><span> #tablespaceinfo
 
</span><span>CLOSE</span><span> Info_cursor  
</span><span>DEALLOCATE</span><span> Info_cursor  
</span><span>DROP</span> <span>TABLE</span> <span>[</span><span>#tablespaceinfo</span><span>]</span>

3. 查询结果

连续的数据记录之间做比较，即可得到数据的增量变化情况

示例代码如下：

;<span>with</span> table_spaceinfo <span>as</span><span> 
(
   </span><span>select</span><span> record_date, record_time, table_name, rows_count, reserved, data_size, index_size, unused
        ,ROW_NUMBER() </span><span>over</span>(PARTITION <span>by</span> table_name <span>order</span> <span>by</span> record_date,record_time <span>asc</span>) <span>as</span><span> list_no
   </span><span>from</span><span> t_rpt_table_spaceinfo
)
</span><span>select</span> _a.table_name <span>as</span> 表名,<span>convert</span>(<span>varchar</span>(<span>20</span>),_a.record_date)<span>+</span><span>'</span> <span>'</span><span>+</span><span>convert</span>(<span>varchar</span>(<span>8</span>),_a.record_time)<span>+</span><span>'</span><span>~~</span><span>'</span>
    <span>+</span><span>convert</span>(<span>varchar</span>(<span>20</span>),_b.record_date)<span>+</span><span>'</span> <span>'</span><span>+</span><span>convert</span>(<span>varchar</span>(<span>8</span>),_b.record_time) <span>as</span> <span>[</span><span>时间段范围</span><span>]</span><span>
    ,_b.rows_count</span><span>-</span>_a.rows_count <span>as</span> <span>[</span><span>新增的行数</span><span>]</span><span>
    ,_b.data_size </span><span>-</span> _a.data_size <span>as</span> <span>[</span><span>新增数据空间(KB)</span><span>]</span>
<span>from</span><span> table_spaceinfo _a
</span><span>join</span> table_spaceinfo _b <span>on</span> _a.table_name<span>=</span>_b.table_name <span>and</span> _a.list_no<span>=</span>_b.list_no<span>-</span><span>1</span>
<span>order</span> <span>by</span> <span>[</span><span>时间段范围</span><span>]</span>

如有不对的地方，欢迎拍砖，谢谢！O(∩_∩)O

mysql安装后如何设置防火墙规则_mysql网络安全配置

mysql数据库中的命令历史记录与权限审计

mysql升级过程中如何防止死锁_mysql并发安全措施

mysql迁移数据时如何处理大表_mysql大表迁移方案

mysql如何防止权限被滥用_mysql权限控制实践

相关专题

batoto漫画官网入口与网页版访问指南

本专题系统整理batoto漫画官方网站最新可用入口，涵盖最新官网地址、网页版登录页面及防走失访问方式说明，帮助用户快速找到batoto漫画官方平台，稳定在线阅读各类漫画内容。

127

2026.02.25

Steam官网正版入口与注册登录指南_新手快速进入游戏平台方法

本专题系统整理Steam官网最新可用入口，涵盖网页版登录地址、新用户注册流程、账号登录方法及官方游戏商店访问说明，帮助新手玩家快速进入Steam平台，完成注册登录并管理个人游戏库。

2026.02.25

TypeScript全栈项目架构与接口规范设计

本专题面向全栈开发者，系统讲解基于 TypeScript 构建前后端统一技术栈的工程化实践。内容涵盖项目分层设计、接口协议规范、类型共享机制、错误码体系设计、接口自动化生成与文档维护方案。通过完整项目示例，帮助开发者构建结构清晰、类型安全、易维护的现代全栈应用架构。

2026.02.25

Python数据处理流水线与ETL工程实战

本专题聚焦 Python 在数据工程场景下的实际应用，系统讲解 ETL 流程设计、数据抽取与清洗、批处理与增量处理方案，以及数据质量校验与异常处理机制。通过构建完整的数据处理流水线案例，帮助开发者掌握数据工程中的性能优化思路与工程化规范，为后续数据分析与机器学习提供稳定可靠的数据基础。

2026.02.25

Java领域驱动设计（DDD）与复杂业务建模实战

本专题围绕 Java 在复杂业务系统中的建模与架构设计展开，深入讲解领域驱动设计（DDD）的核心思想与落地实践。内容涵盖领域划分、聚合根设计、限界上下文、领域事件、贫血模型与充血模型对比，并结合实际业务案例，讲解如何在 Spring 体系中实现可演进的领域模型架构，帮助开发者应对复杂业务带来的系统演化挑战。

2026.02.25

Golang 生态工具与框架：扩展开发能力

《Golang 生态工具与框架》系统梳理 Go 语言在实际工程中的主流工具链与框架选型思路，涵盖 Web 框架、RPC 通信、依赖管理、测试工具、代码生成与项目结构设计等内容。通过真实项目场景解析不同工具的适用边界与组合方式，帮助开发者构建高效、可维护的 Go 工程体系，并提升团队协作与交付效率。

2026.02.24

Golang 性能优化专题：提升应用效率

《Golang 性能优化专题》聚焦 Go 应用在高并发与大规模服务中的性能问题，从 profiling、内存分配、Goroutine 调度、GC 机制到 I/O 与锁竞争逐层分析。结合真实案例讲解定位瓶颈的方法与优化策略，帮助开发者建立系统化性能调优思维，在保证代码可维护性的同时显著提升服务吞吐与稳定性。

2026.02.24