Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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 d.name = '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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.