Database Data and Log File Sizes and Growth
Published: Aug 21, 2016
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,
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 = 'master'
and m.file_id = 1
order by m.size desc

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron