Agent Jobs

Get All Database Sizes in GB (Data, Log, and Total) Rounded

Here’s some SQL logic that will basically give you the data, log, and total (data + log) in GB. Additionally; all sizes are rounded (ceiling).

SQL logic for totals for reference Note: Variances within 1GB are rounded as 1GB and totals between data & log files will not exceed 1gb if fewer than 1GB variances are found.

use master;
set nocount on
select
[name] = upper(sd.name)
,	[data	- in gb]	= ceiling(sum(case when type = 0 then (smf.size*8)/1024.0/1024.0 else 0 end))
,	[log	- in gb]	= ceiling(sum(case when type = 1 then (smf.size*8)/1024.0/1024.0 else 0 end)) ,	[total	- in gb]	= ceiling(sum(smf.size * 8.00/1024.00/1024.00)) from
sys.databases sd join sys.master_files smf on sd.database_id = smf.database_id where
sd.source_database_id is null
and sd.name not in ('master', 'model', 'msdb', 'tempdb')
group by
sd.name
order by
[data	- in gb] desc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s