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

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

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

实现步骤如下：

1. 创建表

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

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

2. 新建作业

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

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

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

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

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

3. 查询结果

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

示例代码如下：

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

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

查看更多关于监控SQLServer数据库表每天的空间变化情况的详细内容...