November 28, 2012 at 1:42 am
Comments posted to this topic are about the item Query to get Database Size and Growth Report
Thanks & Regards,
Santosh R. kamble
November 28, 2012 at 5:00 am
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'
November 28, 2012 at 5:27 am
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'
SELECTd.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
ONfs.database_id = d.database_id
ANDfs.file_id = f.file_id
drop table #FileSpace
April 30, 2015 at 9:38 am
Hi Chris,
Your script is different than the topic.
Thanks.
September 16, 2015 at 2:03 pm
One thing that jumped out at me was how many DBs I have.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply