Monitor the daily space changes of SQL Server database tables

After reading Hua Zi's "share an SQLSERVER script (calculate the data volume of each table in the database and the space occupied by each row of records, I want to use the Code provided in this article to create a statistical table for the number of new rows per day and the function of adding a bucket as follows: 1. create a table, create a table, and store the daily tablespace usage. CREATETABLE [dbo]

After reading Hua Zi's "share an SQLSERVER script (calculate the data volume of each table in the database and the space occupied by each row of records, I want to use the Code provided in this article to create a statistical table for the number of new rows per day and the function of adding a bucket as follows: 1. CREATE a TABLE to CREATE a TABLE and store the daily TABLE space usage. create table [dbo]

After reading Hua Zi's "share an SQLSERVER script (calculate the data volume of each table in the database and the space occupied by each row of records, I want to use the Code provided in this article to create a statistical table that displays the number of new lines per day and the function of adding buckets.

The implementation steps are as follows:

1. Create a table

Create a table to store the daily tablespace usage

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 job

Create a new job. The job is scheduled to run once every morning. the space occupied by the table is recorded every day and stored 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 rows 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 cursor, cyclically obtain table space usage */DECLARE Info_cursor cursorfor select '[' + [name] + ']' FROM sys. tables WHERE type = 'U '; OPEN partition fetch next from partition INTO @ tablename WHILE @ FETCH_STATUS = 0 begin insert into # tablespaceinfo EXEC sp_spaceused @ tablename fetch next from Info_cursor INTO @ tablename end insert into partition (record_date, expiration, expiration, [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

Compare consecutive data records to obtain incremental data changes.

The sample 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 _. table_name as table name, convert (varchar (20), _. record_date) + ''+ convert (varchar (8), _. record_time) + '~~ '+ Convert (varchar (20), _ B. record_date) + ''+ convert (varchar (8), _ B. record_time) as [time range], _ B. rows_count-_. rows_count as [new rows], _ B. data_size-_. data_size as [new data space (KB)] from table_spaceinfo _ ajoin table_spaceinfo _ B on _. table_name = _ B. table_name and _. list_no = _ B. list_no-1order by [time range]

If anything is wrong, please make a brick. Thank you! O (partition _ partition) O