A tab The number of new rows per day and the functionality of the new storage space _mssql

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

The implementation steps are as follows:

1. Create a table

Create a table to store daily table space occupancy

CREATE TABLE [dbo]. [T_rpt_table_spaceinfo] (
[table_name] [sysname] NOT NULL, [
record_date] [date] isn't null,
[record_time] [TIME] (7) is 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. New Job

To create a new job, the job schedule is run every morning, and the space used to record the table is stored in the table established in the previous step.

The T-SQL code executed in the job is:

SET NOCOUNT ON/* Creates a temporary table that holds the space of the user's table and the number of data rows/CREATE TABLE #tablespaceinfo (Nameinfo VARCHAR), Rowsinfo BIGINT, Reserv 

Ed VARCHAR (a), Datainfo VARCHAR (a), Index_size VARCHAR (a), unused VARCHAR ()) DECLARE @tablename VARCHAR (255); 

/* Using cursors, looping the use of tablespace/DECLARE info_cursor cursor for SELECT ' [' + [name] + '] ' from sys.tables WHERE type = ' U '; OPEN info_cursor FETCH NEXT from Info_cursor to @tablename while @ @FETCH_STATUS = 0 BEGIN inserts into #tablespaceinf o EXEC sp_spaceused @tablename FETCH NEXT from Info_cursor to @tablename end inserts into T_rpt_table_spaceinfo (rec Ord_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 the continuous data records

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_r Pt_table_spaceinfo
)
select _a.table_name as table name, CONVERT (varchar (), _a.record_date) + ' +convert (varchar (8) , _a.record_time) + ' ~ ~ '
+convert (varchar), _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
[ Time Period Range]