{"id":242,"date":"2012-12-03T15:21:32","date_gmt":"2012-12-03T15:21:32","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=242"},"modified":"2014-03-06T15:41:24","modified_gmt":"2014-03-06T15:41:24","slug":"report-to-capture-table-growth-statistics-for-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/report-to-capture-table-growth-statistics-for-sql-server\/","title":{"rendered":"Report to Capture Table Growth Statistics for SQL Server"},"content":{"rendered":"

There are a few tools that monitor table size growth, but with the use of sp_spaceused and SQL Server Reporting Services (SSRS) you can make custom reports.<\/p>\n

Using sp_spaceused<\/h3>\n

The system stored procedure sp_spaceused<\/strong> displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database in an object is provided or it displays the disk space reserved and used by the whole database.<\/p>\n

This procedure, sp_spaceused can be used at the database or table level. For example, sp_spaceused at the database level returns the following:<\/p>\n

\"SQL<\/a>
\nAnd sp_spaceused at the table level displays:
\n\"SQL<\/a><\/p>\n

 <\/p>\n

Creating the Report<\/h3>\n

To create our custom report, first we will need to create a table that will store the data from sp_spaceused. For this example, I’ll create a table named TableSizeGrowth.<\/p>\n

\n
CREATE TABLE [dbo].[TableSizeGrowth](\r\n[id] [int] IDENTITY(1,1) NOT NULL,\r\n[table_schema] [nvarchar](256) NULL,\r\n[table_name] [nvarchar](256) NULL,\r\n[table_rows] [int] NULL,\r\n[reserved_space] [int] NULL,\r\n[data_space] [int] NULL,\r\n[index_space] [int] NULL,\r\n[unused_space] [int] NULL,\r\n[date] [datetime] NULL\r\n) ON [PRIMARY]<\/pre>\n<\/div>\n
I’m also going to add a default constraint that will automatically update the date column to the current date for each new row.<\/p>\n
\n
ALTER TABLE [dbo].[TableSizeGrowth] ADD CONSTRAINT [DF_TableSizeGrowth_date]  \r\nDEFAULT (dateadd(day,(0),datediff(day,(0),getdate()))) FOR [date]\r\nGO<\/pre>\n<\/div>\n
Next, we need to create a stored procedure that moves the data from sp_spaceused to the table we created above. I have put comments throughout the stored procedure to help you figure out what is going on. Also make sure you change the table names to match your tables. For this example, I want to monitor HumanResources.Employee, Production.Product, and Purchasing.Vendor tables.<\/p>\n
\n
SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE [dbo].[sp_TableSizeGrowth] \r\nAS\r\nBEGIN\r\n SET NOCOUNT ON\r\n\r\n --DECLARE VARIABLES\r\n DECLARE\r\n @max INT,\r\n @min INT,\r\n @table_name NVARCHAR(256),\r\n @table_schema NVARCHAR(256),\r\n @sql NVARCHAR(4000)\r\n\r\n --DECLARE TABLE VARIABLE\r\n DECLARE @table TABLE(\r\n id INT IDENTITY(1,1) PRIMARY KEY,\r\n table_name NVARCHAR(256),\r\n table_schema NVARCHAR(256))\r\n\r\n --CREATE TEMP TABLE THAT STORES INFORMATION FROM SP_SPACEUSED\r\n IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL\r\n BEGIN\r\n  DROP TABLE #results\r\n END\r\n\r\n CREATE TABLE #results\r\n (\r\n  [table_schema] [nvarchar](256) NULL,\r\n  [table_name] [nvarchar](256) NULL,\r\n  [table_rows] [int] NULL,\r\n  [reserved_space] [nvarchar](55) NULL,\r\n  [data_space] [nvarchar](55) NULL,\r\n  [index_space] [nvarchar](55) NULL,\r\n  [unused_space] [nvarchar](55) NULL\r\n )\r\n\r\n --LOOP THROUGH STATISTICS FOR EACH TABLE\r\n INSERT @table(table_schema, table_name)\r\n SELECT  \r\n  table_schema, table_name\r\n FROM\r\n  information_schema.tables \r\n WHERE table_schema + '.' + table_name IN ('HumanResources.Employee','Production.Product', 'Purchasing.Vendor') --INSERT TABLE NAMES TO MONITOR\r\n\r\n SELECT\r\n  @min = 1,\r\n  @max = (SELECT MAX(id) FROM @table)\r\n\r\n WHILE @min = @max\r\n BEGIN\r\n  SELECT \r\n   @table_name = table_name,\r\n   @table_schema = table_schema\r\n  FROM\r\n   @table\r\n  WHERE\r\n   id = @min\r\n\r\n  --DYNAMIC SQL\r\n  SELECT @sql = 'EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']'''\r\n\r\n  --INSERT RESULTS FROM SP_SPACEUSED TO TEMP TABLE\r\n  INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)\r\n  EXEC (@sql)\r\n\r\n  --UPDATE SCHEMA NAME\r\n  UPDATE #results\r\n  SET table_schema = @table_schema\r\n  WHERE table_name = @table_name\r\n  SELECT @min = @min + 1\r\n END\r\n\r\n --REMOVE \"KB\" FROM RESULTS FOR REPORTING (GRAPH) PURPOSES\r\n UPDATE #results SET data_space = SUBSTRING(data_space, 1, (LEN(data_space)-3))\r\n UPDATE #results SET reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))\r\n UPDATE #results SET index_space = SUBSTRING(index_space, 1, (LEN(index_space)-3))\r\n UPDATE #results SET unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))\r\n\r\n --INSERT RESULTS INTO TABLESIZEGROWTH\r\n INSERT INTO TableSizeGrowth (table_schema, table_name, table_rows, reserved_space, data_space, index_space, unused_space)\r\n SELECT * FROM #results\r\n\r\n DROP TABLE #results\r\nEND<\/pre>\n<\/div>\n
Now that the stored procedure has been created, let’s go ahead and execute it.<\/p>\n
Once the stored procedure finishes we can view the table:<\/p>\n
\"SQL<\/a>
\nClick here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
There are a few tools that monitor table size growth, but with the use of sp_spaceused and SQL Server Reporting Services (SSRS) you can make custom reports. Using sp_spaceused The system stored procedure sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[25,29],"tags":[105,32,31,57],"class_list":["post-242","post","type-post","status-publish","format-standard","hentry","category-monitoring","category-reporting-services-ssrs","tag-monitoring","tag-sql-server-reporting-services","tag-ssrs","tag-tuning"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=242"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/242\/revisions"}],"predecessor-version":[{"id":246,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/242\/revisions\/246"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}