Monitor/alert - Table row growth

• SQL!$@w$0ME

  SSChampion

  Points: 12349

  More actions

  June 22, 2016 at 9:20 am

  #327388

  Hi Folks,

  Anyone has a script/documentation to monitor/alert table growth by percentage or number of rows.

  Many thanks.

• rvsc48

  SSCertifiable

  Points: 7685

  More actions

  June 23, 2016 at 6:30 am

  #1886505

  See if this will help you -

  CREATE TABLE [dbo].[TableSizeGrowth](

  [id] [int] IDENTITY(1,1) NOT NULL,

  [table_schema] [nvarchar](256) NULL,

  [table_name] [nvarchar](256) NULL,

  [table_rows] [int] NULL,

  [reserved_space] [int] NULL,

  [data_space] [int] NULL,

  [index_space] [int] NULL,

  [unused_space] [int] NULL,

  [date] [datetime] NULL

  ) ON [PRIMARY]

  ALTER TABLE [dbo].[TableSizeGrowth] ADD CONSTRAINT [DF_TableSizeGrowth_date]

  DEFAULT (dateadd(day,(0),datediff(day,(0),getdate()))) FOR [date]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE [dbo].[sp_TableSizeGrowth]

  AS

  BEGIN

  SET NOCOUNT ON

  DECLARE

  @max-2 INT,

  @min-2 INT,

  @table_name NVARCHAR(256),

  @table_schema NVARCHAR(256),

  @sql NVARCHAR(4000)

  DECLARE @table TABLE(

  id INT IDENTITY(1,1) PRIMARY KEY,

  table_name NVARCHAR(256),

  table_schema NVARCHAR(256))

  IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL

  BEGIN

  DROP TABLE #results

  END

  CREATE TABLE #results

  (

  [table_schema] [nvarchar](256) NULL,

  [table_name] [nvarchar](256) NULL,

  [table_rows] [int] NULL,

  [reserved_space] [nvarchar](55) NULL,

  [data_space] [nvarchar](55) NULL,

  [index_space] [nvarchar](55) NULL,

  [unused_space] [nvarchar](55) NULL

  )

  INSERT @table(table_schema, table_name)

  SELECT

  table_schema, table_name

  FROM

  information_schema.tables

  WHERE table_schema + '.' + table_name IN () --insert your table names here

  SELECT

  @min-2 = 1,

  @max-2 = (SELECT MAX(id) FROM @table)

  WHILE @min-2 = @max-2

  BEGIN

  SELECT

  @table_name = table_name,

  @table_schema = table_schema

  FROM

  @table

  WHERE

  id = @min-2

  SELECT @sql = 'EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']'''

  INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)

  EXEC (@sql)

  UPDATE #results

  SET table_schema = @table_schema

  WHERE table_name = @table_name

  SELECT @min-2 = @min-2 + 1

  END

  UPDATE #results SET data_space = SUBSTRING(data_space, 1, (LEN(data_space)-3))

  UPDATE #results SET reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))

  UPDATE #results SET index_space = SUBSTRING(index_space, 1, (LEN(index_space)-3))

  UPDATE #results SET unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))

  INSERT INTO TableSizeGrowth (table_schema, table_name, table_rows, reserved_space, data_space, index_space, unused_space)

  SELECT * FROM #results

  DROP TABLE #results

  END

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  June 23, 2016 at 3:34 pm

  #1886637

  SQL!$@w$0ME (6/22/2016)

  ---

  Hi Folks,

  Anyone has a script/documentation to monitor/alert table growth by percentage or number of rows.

  Many thanks.

  Based on what and when? The condition just before'n'after a given insert or some previous point in time?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• johnwalker10

  SSCrazy Eights

  Points: 9124

  More actions

  June 23, 2016 at 10:29 pm

  #1886650

  Take a look at this link, hope this will help you: http://www.sql-server-performance.com/2016/monitor-data-growth/[/url]