SQLServer : What are databases and their sizes ?
How do you quickly find out how big each of the databases in a SQLServer instance are and when they were last backed up ?
with DbFiles as ( SELECT DB_NAME(database_id) AS DatabaseName ,Name AS LogicalName ,Physical_Name as PhysicalName , (size*8)/1024 SizeMB FROM sys.master_files ), LastBackup as ( SELECT @@SERVERNAME as ServerName , T1.Name as DatabaseName , COALESCE(Convert(varchar(12), MAX(T2.backup_finish_date), 101),'Not Yet Taken') as LastBackup , COALESCE(Convert(varchar(12), MAX(T2.user_name), 101),'NA') as UserName FROM sys.sysdatabases T1 LEFT OUTER JOIN msdb.dbo.backupset T2 ON T2.database_name = T1.name GROUP BY T1.name ) select dbf.DatabaseName, sum(dbf.SizeMB) as SizeMB, MAX(lb.LastBackup) as LastBackup from DbFiles as dbf left outer join LastBackup as lb on dbf.DatabaseName = lb.DatabaseName group by dbf.DatabaseName order by SizeMb desc;
The above includes log files in the total size, but realistically those must be counted for most planning operations.
This is a good place to start when assessing a server for the first time. Whats on it, how big is it, is it backed up.