asked on

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,

Vikas Garg

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 Prasad

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 Chauhan

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/

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial