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
Categories: Agent Jobs, Alerts, Automations, Backup & Restore, Command Prompt, Database Mail, ELI5, Error Solutions, Featured, Folder System, Forensics & Auditing, High Availability, Long Scripts, Management Studio, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds