May 5, 2015 at 12:20 am
Hello Masters,
I need to monitor my database growth, as few of databases are growing rapidly. My client wants the growth list of my databases. Please help me to have report of database growth of specific databases, at least of one month.
Thanks in advance.
Jeet
May 5, 2015 at 12:26 am
query the database size, insert into table with timestamp... I know there are examples around here somewhere. Search around.
May 5, 2015 at 2:13 am
You can rely on database size recorded by backup information on msdb.dbo.backupset.
Otherwise, you can set up the data collector and enable the disk usage collection set.
-- Gianluca Sartori
May 5, 2015 at 7:23 am
Here is one way to do it, create this procedure in a suitable DB on your server (its basically a stripped out version of sp_spaceused):
IF EXISTS (SELECT TOP 1 1 from sys.procedures WHERE name = 'p_audit_dbsize')
DROP PROCEDURE p_audit_dbsize
GO
CREATE PROCEDURE p_audit_dbsize AS
BEGIN
SET NOCOUNT ON
DECLARE @MyResults TABLE(
ServerName sysname,
DBName sysname,
DBSizeInMB decimal(38,2),
UnAllocatedSpaceInMB decimal(38,2),
ReservedSizeInMB int NULL,
DataSizeInMB int NULL,
IndexSizeInMB int NULL,
UnUsedSizeInMB int NULL,
DateLogged datetime NULL)
INSERT INTO @MyResults
exec sp_msforeachdb '
IF (SELECT CASE WHEN ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''distribution'',''Sysadmin'') THEN 1 ELSE 0 END) = 1
BEGIN
DECLARE@typecharacter(2) -- The object type.
,@pagesbigint-- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
select@dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)),
@logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from [?].dbo.sysfiles
select@reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index-Docid is not considered "data", but is part of "index_size"
When it.internal_type IN (202,204) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
From [?].sys.partitions p join [?].sys.allocation_units a on p.partition_id = a.container_id
left join [?].sys.internal_tables it on p.object_id = it.object_id
SELECT @@SERVERNAME as SQLInstance,
''?'' as DatabaseName ,
ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) * 8192 / 1048576,15,2) ) AS DBSizeInMB,
ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2) ) UnAllocatedSpaceInMB,
(ltrim(str(@reservedpages * 8192 / 1024.,15,0) )/1024) AS ReservedSizeInMB,
(ltrim(str(@pages * 8192 / 1024.,15,0) ) /1024) as DataSizeInMB,
(ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) )/1024) AS IndexSizeInMB,
(ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0)) /1024) AS UnUsedSizeInMB,
getdate() As DateLogged
END
'
SELECTServerName,
DBName,
DBSizeInMB,
UnAllocatedSpaceInMB,
ReservedSizeInMB,
DataSizeInMB ,
IndexSizeInMB,
UnUsedSizeInMB,
DateLogged
FROM @MyResults
END
GO
You can then call the procedure, get the result set and store it in a table, over time it will give you your growth history. Note the line in the proc that filters out a list of DB's that you may not want to monitor.
You can also set up a similar process for tables using this proc:
IF EXISTS (SELECT TOP 1 1 FROM sys.procedures WHERE name = 'p_audit_tablesize')
DROP PROCEDURE p_audit_tablesize
GO
CREATE PROCEDURE p_audit_tablesize AS
BEGIN
SET NOCOUNT ON
DECLARE @TrendTableStats TABLE (
DBName sysname NULL,
TableName sysname,
NoRows bigint,
ReservedSizeInKB varchar(1000),
DataSizeInKB varchar(1000),
IndexSizeInKB varchar(1000),
UnUsedSizeInKB varchar(1000),
DateLogged varchar(1000) DEFAULT getdate()
)
DECLARE @DB sysname
DECLARE @sqlstmt nvarchar(4000)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution', 'Sysadmin')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sqlstmt = 'USE ' + @DB + ' exec sp_msforeachtable ''exec sp_spaceused ''''?'''''''
INSERT INTO @TrendTableStats (TableName, NoRows, ReservedSizeInKB, DataSizeInKB, IndexSizeInKB, UnUsedSizeInKB)
EXEC (@sqlstmt)
UPDATE @TrendTableStats
SET DBName = @DB
WHERE DBName IS NULL
FETCH NEXT FROM db_cursor INTO @DB
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT@@SERVERNAME AS Server,
DBName,
TableName,
NoRows,
CAST(REPLACE(ReservedSizeInKB,' KB','') As bigint) AS ReservedSizeInKB,
CAST(REPLACE(DataSizeInKB,' KB','') As bigint) AS DataSizeInKB ,
CAST(REPLACE(IndexSizeInKB,' KB','') As bigint) AS IndexSizeInKB ,
CAST(REPLACE(UnUsedSizeInKB,' KB','') As bigint) AS UnUsedSizeInKB,
DateLogged
FROM @TrendTableStats
END
GO
exec p_audit_tablesize
MCITP SQL 2005, MCSA SQL 2012
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply