Skip to content

Instantly share code, notes, and snippets.

@ghotz

ghotz/get-all-dbs-size.sql

Created January 23, 2021 12:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ghotz/f33efd1d2a65ba07e4ab8bf550ffd67e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ghotz/f33efd1d2a65ba07e4ab8bf550ffd67e to your computer and use it in GitHub Desktop.
Download ZIP
Get size of data, log and others for all databases ordered by decreasing data size
Raw
get-all-dbs-size.sql
SELECT
DB_NAME(database_id) AS database_name
, SUM(CASE WHEN type_desc = 'ROWS' THEN size / 128.8 ELSE 0 END) space_data_mb
, SUM(CASE WHEN type_desc = 'LOG' THEN size / 128.8 ELSE 0 END) space_log_mb
, SUM(CASE WHEN type_desc NOT IN('ROWS', 'LOG') THEN size / 128.8 ELSE 0 END) space_other_mb
FROM sys.master_files
GROUP BY
DB_NAME(database_id)
ORDER BY space_data_mb DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment