Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Get all your databases and their sizes

SELECT      @@SERVERNAME AS SqlServerInstance,
            db.name AS DatabaseName,
            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize,
            SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize,
            SUM(af.size / 128.0E) AS TotalSize
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')   -- Sample databases
GROUP BY    db.name

Print | posted on Thursday, February 12, 2009 11:20 AM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: Get all your databases and their sizes

great, thank you. How about script about table space distribution? :)
2/12/2009 5:25 PM | Remote DBA
Gravatar

# re: Get all your databases and their sizes

there is a typo - this worked for me in 2008:
SELECT @@SERVERNAME AS SqlServerInstance,
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 LogSize,
SUM(af.size / 128.0E) AS TotalSize
FROM master.sys.databases AS db
INNER JOIN master.sys.sysaltfiles AS af ON af.dbid = db.database_id
WHERE db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases
AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW') -- Sample databases
GROUP BY db.name
ORDER BY db.name

2/20/2009 7:49 PM | Dave Satz
Gravatar

# re: Get all your databases and their sizes

It's not a typo. The FROM and INNER JOIN parts are reading from compatibility views in SQL Server 2005/2008 and tables in SQL Server 2000.

The script works for me with SQL Server 2000, SQL Server 2005 and SQL Server 2008 as is.
3/12/2009 1:41 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET