db growth

Question

db growth

ramyours2003

SSChampion

Points: 12268
More actions
November 25, 2016 at 6:38 am

#315198

I need query to get the database growth to analyse in the format date , Size of the backup file GB , growth GB and Max size of datafile .
December 1, 2016 at 9:49 pm

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Use the queries below to get the desired information.

Execute it at regular intervals (daily?) and store the results. Build a reporting query on these stored results to get the differences in size over time.

SELECT Db_name(database_id) AS database_name

, NAME AS file_name

, type_desc

, Cast(size * 8.0 / 1024 AS DECIMAL(10, 2)) AS size_MB

, Cast(max_size * 8.0 / 1024 AS DECIMAL(10, 2)) AS max_size_MB

, GetDate() as inquiry_date

FROM sys.master_files;

SELECT database_name

, backup_size

, compressed_backup_size

, GetDate() as inquiry_date

FROM msdb..backupset;

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

kevaburg SSCoach Points: 18131 More actions · Answer 2

JasonClark (12/1/2016)
you may use this command SP_TRACK_GROWTH

Where did you get this? I am not aware that this even exists.....

Sue_H SSC Guru Points: 90860 More actions · Answer 3

kevaburg (12/2/2016)
JasonClark (12/1/2016)
you may use this command SP_TRACK_GROWTH
Where did you get this? I am not aware that this even exists.....

It doesn't, not as a built in function anyway. There was one stored procedure used by many over ten years ago - the only reason I remember it. Then some modifications by others, etc.

I doubt any version use the current DMVs. Here is the original if you are interested - date is 2001. But the guy did have some very good scripts on his site back in the day:

http://vyaskn.tripod.com/code/sp_track_db_growth.txt

Sue

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

HanShi (11/25/2016)
Use the queries below to get the desired information.
Execute it at regular intervals (daily?) and store the results. Build a reporting query on these stored results to get the differences in size over time.
SELECT Db_name(database_id) AS database_name
, NAME AS file_name
, type_desc
, Cast(size * 8.0 / 1024 AS DECIMAL(10, 2)) AS size_MB
, Cast(max_size * 8.0 / 1024 AS DECIMAL(10, 2)) AS max_size_MB
, GetDate() as inquiry_date
FROM sys.master_files;
SELECT database_name
, backup_size
, compressed_backup_size
, GetDate() as inquiry_date
FROM msdb..backupset;

Just a bit of a simplification...

SELECT Db_name(database_id) AS database_name

, NAME AS file_name

, type_desc

, Cast(size / 128.0 AS DECIMAL(10, 2)) AS size_MB

, Cast(max_size / 128.0 AS DECIMAL(10, 2)) AS max_size_MB

, GetDate() as inquiry_date

FROM sys.master_files;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)