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

WBOY

リリース： 2016-06-07 15:44:43

オリジナル

1512 人が閲覧しました

阅读完桦仔的《分享一个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代码为：

<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