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

来源：懂视网责编：小采时间：2020-11-09 07:38:47

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

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

推荐度：

点击下载本文 文档为doc格式

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

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

声明：本网页内容旨在传播知识，若有侵权等问题请及时与本网联系，我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com