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 |
Leave a Comment