;WITH DBSize (SqlServerInstanceName, DatabaseName, DatabaseSize, DBLogSize, TotalDBSize)
AS
(
SELECT @@SERVERNAME SqlServerInstanceName,
db.name AS DatabaseName,
SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize,
SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS DBLogSize,
SUM(af.size / 128.0E) AS TotalDBSize
FROM master..sysdatabases AS db
INNER JOIN master..sysaltfiles AS af ON af.[dbid] = db.[dbid]
WHERE db.name NOT IN (‘distribution’, ‘Resource’, ‘master’, ‘tempdb’, ‘model’, ‘msdb’) — System databases
AND db.name NOT IN (‘Northwind’, ‘pubs’, ‘AdventureWorks’, ‘AdventureWorksDW’)
GROUP BY db.name
)
SELECT * FROM DBSize order by TotalDBSize desc
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.