A simple way is to have a log table, updated nightly. Just create a table and a stored proc as below and have a job that runs it every night.
You can set a database auto-growth setting by using SQL Server Management Studio, scripted SMO, or by using T-SQL when you create your database. You can also use these methods to change the auto-growth settings of existing databases. Let me show you how to check the auto-grow database size using SQL Server Management Studio.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT name AS FileName, size*1.0/128 AS FileSizeinMB, 'MaximumSizeinMB' = CASE max_size WHEN 0 THEN 'No growth is allowed.' WHEN -1 THEN 'Autogrowth is on.' WHEN 268435456 THEN 'Log file will grow to a maximum size of 2 TB.'' ELSE CAST (max_size*1.0/128 AS nvarchar(30)) END, growth AS 'GrowthValue', 'GrowthIncrement' = CASE WHEN growth = 0 THEN 'File size is fixed and will not grow.' WHEN growth > 0 AND is_percent_growth = 0 THEN 'Growth value is in units of 8-KB pages.' ELSE 'Growth value is a percentage.' END FROM sys.database_files |
The example here runs the size query twice for two different databases on the same server. You can then have a simple report off the back of this showing the growth trends over time and on a weekly basis for the largest and the fastest-growing tables.
Table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE [dbo].[tb_TableSize]( [Id] [int] IDENTITY(1000,1) NOT NULL, [DB] [varchar](2) NOT NULL, [table_id] [int] NOT NULL, [table_name] [sysname] NOT NULL, [rows] [int] NULL, [total_space_MB] [int] NULL, [data_space_MB] [int] NULL, [index_space_MB] [int] NULL, [unused_space_MB] [int] NULL, [query_date] [smalldatetime] NULL, CONSTRAINT [PK_tb_TableSize] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] |
Procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
CREATE PROCEDURE [Job].[proc_TableSizeINSERT] AS BEGIN set nocount on declare @dt smalldatetime set @dt = getutcdate() INSERT INTO [CommunicatorV4DataWarehouse].[dbo].[tb_TableSize] ([DB] ,[table_id] ,[table_name] ,[rows] ,[total_space_MB] ,[data_space_MB] ,[index_space_MB] ,[unused_space_MB] ,[query_date]) SELECT 'V4' as DB, table_id = [object_id], table_name = [name], rows = [rowCount], total_space_MB = reservedpages * 8/1000, data_space_MB = pages * 8/1000, index_space_MB = (CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8/1000, unused_space_MB = (CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8/1000, query_date = @dt from ( SELECT o.[Name], [object_id], reservedpages = SUM (reserved_page_count), usedpages = SUM (used_page_count), pages = SUM ( CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END ), [rowCount] = SUM ( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) FROM CommunicatorV4.sys.dm_db_partition_stats s inner join CommunicatorV4..sysobjects o on s.[object_id] = o.id where type = 'U' group by [object_id], o.[name] ) DBData INSERT INTO [CommunicatorV4DataWarehouse].[dbo].[tb_TableSize] ([DB] ,[table_id] ,[table_name] ,[rows] ,[total_space_MB] ,[data_space_MB] ,[index_space_MB] ,[unused_space_MB] ,[query_date]) SELECT 'DW' as DB, table_id = [object_id], table_name = [name], rows = [rowCount], total_space_MB = reservedpages * 8/1000, data_space_MB = pages * 8/1000, index_space_MB = (CASE WHEN usedpages > pages THEN (usedpages - pages) ELSE 0 END) * 8/1000, unused_space_MB = (CASE WHEN reservedpages > usedpages THEN (reservedpages - usedpages) ELSE 0 END) * 8/1000, query_date = @dt from ( SELECT o.[Name], [object_id], reservedpages = SUM (reserved_page_count), usedpages = SUM (used_page_count), pages = SUM ( CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END ), [rowCount] = SUM ( CASE WHEN (index_id < 2) THEN row_count ELSE 0 END ) FROM CommunicatorV4DataWarehouse.sys.dm_db_partition_stats s inner join CommunicatorV4DataWarehouse..sysobjects o on s.[object_id] = o.id where type = 'U' group by [object_id], o.[name] ) DBData --truncate table CommunicatorV4DataWarehouse.dbo.tb_TableSize END |
The article was published on February 18, 2017 @ 3:55 AM
Leave a Comment