Any free or simple solution to track the growth of an SQL database

Hi

I have a customer who is concern that his cloud server storage space due to the growth rate of the SQL server.

I have suggested that he periodically checks the database size but he is not keen on the idea.

Is there a SQL script that I can use to write to periodically so the user can analyze the growth rate of the database? Alternatively, are there any free tools that can assist the tracking of the database growth size?  

Thank yuo,
NaviWorldSGAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vikas GargAssociate Principal EngineerCommented:
Hi,

USE master
GO

CREATE PROC sp_track_db_growth
(
@dbnameParam sysname = NULL
)
AS
BEGIN


DECLARE @dbname sysname

/* Work with current database if a database name is not specified */

SET @dbname = COALESCE(@dbnameParam, DB_NAME())

SELECT	CONVERT(char, backup_start_date, 111) AS [Date], --yyyy/mm/dd format
	CONVERT(char, backup_start_date, 108) AS [Time],
	@dbname AS [Database Name], [filegroup_name] AS [Filegroup Name], logical_name AS [Logical Filename], 
	physical_name AS [Physical Filename], CONVERT(numeric(9,2),file_size/1048576) AS [File Size (MB)],
	Growth AS [Growth Percentage (%)]
FROM
(
	SELECT	b.backup_start_date, a.backup_set_id, a.file_size, a.logical_name, a.[filegroup_name], a.physical_name,
		(
			SELECT	CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)
			FROM	msdb.dbo.backupfile i1
			WHERE 	i1.backup_set_id = 
						(
							SELECT	MAX(i2.backup_set_id) 
							FROM	msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
								ON i2.backup_set_id = i3.backup_set_id
							WHERE	i2.backup_set_id < a.backup_set_id AND 
								i2.file_type='D' AND
								i3.database_name = @dbname AND
								i2.logical_name = a.logical_name AND
								i2.logical_name = i1.logical_name AND
								i3.type = 'D'
						) AND
				i1.file_type = 'D' 
		) AS Growth
	FROM	msdb.dbo.backupfile a JOIN msdb.dbo.backupset b 
		ON a.backup_set_id = b.backup_set_id
	WHERE	b.database_name = @dbname AND
		a.file_type = 'D' AND
		b.type = 'D'
		
) as Derived
WHERE (Growth <> 0.0) OR (Growth IS NULL)
ORDER BY logical_name, [Date]

END

Open in new window


Create this stored procedure and

EXEC sp_track_db_growth
GO

to check growth or current db

EXEC sp_track_db_growth 'dbname'
GO

for required db.

You can store the result in table and use that table as tracking one
Kanti PrasadCommented:
Hi

If you have some history then you can query the msdb.dbo.backupset and the below code in the side will get you do it

http://theadeptdba.blogspot.ie/2013/01/how-fast-is-my-sql-server-database.html

Or you can use  trace to get information about recent growth events .

http://dba.stackexchange.com/questions/7788/how-to-check-the-growth-of-database
Deepak ChauhanSQL Server DBACommented:
You can check the backupset table in msdb

This script from SQLskill.com
SELECT
 CAST([database_name] as Varchar(20)) AS 'Database',
 convert(varchar(7),[backup_start_date],120) AS 'Month',
 STR(AVG([backup_size]/1024/1024/1024),5,2) AS 'Backup Size GB',
 STR(AVG([compressed_backup_size]/1024/1024/1024),5,2) AS 'Compressed Backup Size GB',
 STR(AVG([backup_size]/[compressed_backup_size]),5,2) AS 'Compression Ratio'
 FROM msdb.dbo.backupset
 WHERE [database_name] in (select name from sys.databases)
 AND [type] = 'D'
 and backup_finish_date > (getdate()-30)
 GROUP BY [database_name],convert(varchar(7),[backup_start_date],120)
 order BY [database_name],convert(varchar(7),[backup_start_date],120);

Open in new window


Or you can use the script with step by step configuration from this link which i use.

https://sqljourney.wordpress.com/2013/02/13/sql-server-track-database-size-growth-trend/
David ToddSenior Database AdministratorCommented:
Hi,

I track the total data size and log size of the files for each database for each server I manage, and store this in a log table.

A cross join gives me the history for as many days as I want ...

HTH
  David
select 
	getdate() as LogDateTime
	, serverproperty( 'MachineName' ) as MachineName
	, serverproperty( 'ServerName' ) as ServerName
	, serverproperty( 'ProductVersion' ) as ProductVersion
	, serverproperty( 'ProductLevel' ) as ProductLevel
    , serverproperty( 'Collation' ) as ServerCollation
	, cast( serverproperty( 'Edition' ) as varchar( 50 ) ) as Edition
	, cast( sd.name as nvarchar( 128 ) ) as DatabaseName
	, sum( fi.size ) * 8 / 1024.0 / 1024.0 as DataSizeGB
	, sum( ti.size ) * 8 / 1024.0 / 1024.0 as LogSizeGB
    , databasepropertyex( sd.name, 'Collation' ) as DatabaseCollation          
from master.dbo.sysdatabases sd
left outer join master.dbo.sysaltfiles fi
	on fi.dbid = sd.dbid
	and fi.groupid > 0 -- not logs
left outer join master.dbo.sysaltfiles ti
	on ti.dbid = sd.dbid
	and ti.groupid = 0 -- logs
group by
    sd.name

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.