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


Query to get Database Size and Growth Report


Query to get Database Size and Growth Report

Author
Message
kamble_sr
kamble_sr
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 69
Comments posted to this topic are about the item Query to get Database Size and Growth Report

Thanks & Regards,
Santosh R. kamble
Chris Burton-434978
Chris Burton-434978
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 457
In sys.master_files there is a flag called is_percent_growth which is set to 1 if the file growth is in %

You can therefore work out the filegrowth as something like:

CASE WHEN f.is_percent_growth = 1
THEN CONVERT(VARCHAR(3), f.growth) + ' %'
ELSE CONVERT (VARCHAR(5), f.growth / 128) + ' MB'
END AS 'Growth'
Chris Burton-434978
Chris Burton-434978
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 457
I've just written this query for auditing database files... haven't thoroughly tested it yet so let me know if there are any issues

CREATE TABLE #FileSpace (
[database_id] int,
[file_id] int,
[space_used] int
)
INSERT INTO #FileSpace EXEC sp_MSforeachdb 'USE ? SELECT db_id(''?''), fileid, FILEPROPERTY(name, ''SpaceUsed'') from sys.sysfiles'

SELECT d.name AS 'Database Name'
, f.name AS 'Logical File Name'
, f.physical_name AS 'Physical File Name'
, f.[file_id] AS 'File Id'
, f.type_desc AS 'Description'
, f.size / 128 AS 'File Size (MB)'
, CASE f.max_size
WHEN -1 THEN 'Unlimited'
ELSE CONVERT(VARCHAR, (f.max_size / 128))
END AS 'Maximum Size (MB)'
, fs.space_used / 128 AS 'Used Space (MB)'
, (f.size - fs.space_used) / 128 AS 'Free Space (MB)'
, CASE f.is_read_only
WHEN 1 THEN 'Read Only'
ELSE 'Read\Write'
END AS 'Read Only'
, CASE f.is_sparse
WHEN 1 THEN 'Sparse'
ELSE 'Not Sparse'
END AS 'Sparse File'
, CASE WHEN f.is_percent_growth = 1
THEN CONVERT(VARCHAR(3), f.growth) + ' %'
ELSE CONVERT (VARCHAR(5), f.growth / 128) + ' MB'
END AS 'Growth'
FROM sys.master_files f
JOIN sys.databases d
ON f.database_id = d.database_id
JOIN #FileSpace fs
ON fs.database_id = d.database_id
AND fs.file_id = f.file_id

drop table #FileSpace
SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14824 Visits: 3720
Hi Chris,

Your script is different than the topic.

Thanks.
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31816 Visits: 885
One thing that jumped out at me was how many DBs I have.
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