Database Data and Log File Sizes and Growth
Published: 5th December 2014
This isn’t going to anything in depth around the subject such as what you should or shouldn’t have as your standard settings etc. but instead it’s actually just a handy piece of code I knocked together which I happen to use quite a lot, especially when in a new company, just to see how SQL Server is set up.

All the code does is tell you the database name, data size, log size, the growth settings, and what recovery model the database is in.

It’s nothing too complicated, but it can be very handy indeed.

So, as there’s not too much else to say about the code itself, we’ll just get straight to it. Hopefully this will be as useful to you as it’s been to me.

I’ve included the output for a selection of my own databases on my test server so that you can see how the output is presented…

select db_name(m.database_id) dbName, convert(varchar(10), m.size/128) + 'MB' dbSize,
              
case when m.is_percent_growth = 1 then convert(varchar(10), m.growth) + '%' else convert(varchar(10), m.growth/128) + ' MB' end dbGrowth,
              
convert(varchar(10), m1.size/128) + 'MB' logSize, case when m1.is_percent_growth = 1 then convert(varchar(10), m1.growth) + '%' else convert(varchar(10), m1.growth/128) + ' MB' end logGrowth,
              
recovery_model_desc
from sys.master_files m
join sys.master_files m1
on m.database_id = m1.database_id
join sys.databases d
on m.database_id = d.database_id
where m.file_id != m1.file_id
-- and d.name = 'master'
and m.file_id = 1
order by m.size desc
go


Comments:
NB: Comments will only appear once they have been moderated.