SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


track the growth of SQL Database..


track the growth of SQL Database..

Author
Message
igsri
igsri
SSC-Enthusiastic
SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)SSC-Enthusiastic (108 reputation)

Group: General Forum Members
Points: 108 Visits: 58
In my previous company, i was asked to track the growth rate of our database because we had a daily transaction table that gets updated everyday..

So i wrote the following procedure and it is also tested on SQL Server 7.0 & SQL Server 2000.

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

/***********************************************************************************************************

Purpose: To calulate the file growth percentages for a given database and to show you the rate at which
your databases are growing, so that you can plan ahead for your future storage needs.


Tested on: SQL Server 7.0, SQL Server 2000


Usage: Run this script in the master database to create the stored procedure. Once it is created,
you could run it from any of your user databases. If the first parameter (database name) is
not specified, the procedure will use the current database.

Example 1:
To see the file growth information of the current database:

EXEC sp_track_db_growth

Example 2:
To see the file growth information for pubs database:

EXEC sp_track_db_growth 'pubs'

***********************************************************************************************************/

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
David Benoit
David Benoit
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16826 Visits: 3650
Not sure if you knew this but you can actually post scripts to the Script section on this site for others to find and use long term so, you might want to check that out. Left hand side of the page, select Scripts and I think there is a link to post them there.

Thanks for sharing!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
DBA Cabuloso
DBA Cabuloso
SSC Eights!
SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)SSC Eights! (992 reputation)

Group: General Forum Members
Points: 992 Visits: 274
Nice job.

But in 2005 and 2008 you could do it using CTE (Common table Expressions) instead of dierived tables, because the script costs much time if there is a great numberes of backups done.

Thanks anyway

Lucas Benevides
DBA Cabuloso

________________
DBA Cabuloso
Lucas Benevides
zach_john
zach_john
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2326 Visits: 351
Agree with all it is a nice script for 2K and under. Well done!

Unfortunately we never do backups so it would be moot here :-D


John Zacharkan
dishan
dishan
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 39
you can find the same SP here Tongue

http://vyaskn.tripod.com/code/sp_track_db_growth.txt
[url=http://vyaskn.tripod.com/code/sp_track_db_growth.txt][/url]
SQL-DBA-01
SQL-DBA-01
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17318 Visits: 3789
This is an effective way to find DB growth for sure, but if the backup job failed for 10 days and none had addressed it (rare case), then of no use.

Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search