Sometimes we need to find out the size of the database on a server. Below SQL Scripts can be used to find out the size of all the databases created on the server.
;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
;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
