The function of adding rows and storage space in a statistical table every day

Time:2021-10-10

Use the code provided in the article to make a statistical table, the number of new rows per day and the function of new storage space

The implementation steps are as follows:

1. Create table

Create a table to store the table space usage every day


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. Create a new job

Create a new job. The job plan runs once every morning. Record the space occupied by the table every day and store it in the table created in the previous step

The T-SQL code executed in the job is:

SET NOCOUNT ON 
/*Create a temporary table to store the space and data row number information of the user table*/
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); 

/*Use cursors to cycle through the table space usage*/ 
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. Query results

The incremental change of data can be obtained by comparing continuous data records

The example code is as follows:

;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 table name, 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 [time period range]
,_ b.rows_ count-_ a.rows_ Count as [new rows]
,_ b.data_ size - _ a.data_ Size as [new data space (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