SQLServer : What are databases and their sizes ?

by bitznbitez

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.

Advertisements