This script looks much more intimidating than the results it produces, so I recommend running it before reading through it.  I’ll run this when I first get on a unfamiliar server as a basic assessment of what’s there and find some hard to find issues with no effort.

The issues you’ll find include:

• Backups – Where are they, when did they last occur, how big, etc.
  • I always look for databases (excluding model) that are in full or bulk logged recovery with no log backups.  This is a common issue, especially with vendor databases, that will fill your drives with data you didn’t care to have.
    • To know how critical it is when you find this, and you probably will, I include the log size and percent full.
  • There are variables in this script for a cutoff date which will ignore old backups. If you set @use_cutoffdate to 1 then any database not backed up since the value of @cutoffdate will show up as never being backed up. I love this feature because there’s no chance that you’ll misread a year-old backup as being taken last night.
  • Warning, the size of the backup is the size of the data, not the backup file.  To keep this compatible with SQL 2005, I’m not grabbing the compressed_backup_size field.
  • Differential backup results are commented out for a reason…most people don’t use them.  There is absolutely no reason to comment this out other than limiting the number of columns in my results.
• Compatibility Level – If you upgrade a server it’s easy to leave a database behind.  I’ve seen SQL 2000 compatibility on SQL 2008 R2 for no other reason than “oops”.
• Collation name – If this isn’t consistent you could run into issues that are next to impossible to debug.  The exceptions, ironically, are Microsoft databases.  Report Server and SharePoint databases specifically will be a different collation than anything else, but they were written with that in mind and handle it gracefully and shouldn’t be changed.
• Auto Close – Ok, I admit it, I actually have this one commented out in my personal copy of this code.  If this is turned on it will typically blow up your error logs and make them half-way unreadable, and I’ll see this when I attempt to read them.  However, it’s good to know if this is ever turned on as it will kill performance.  I should uncomment this in my version with the mentality that too much information will slightly annoy you, too little will kill you.

DECLARE @cutoffdate datetime
DECLARE @use_cutoffdate bit
SET @cutoffdate = DateAdd(Month, -3, GetDate())
SET @use_cutoffdate = 0
IF Object_ID('TempDB..##FileGroupSize') IS NOT NULL BEGIN
    DROP TABLE ##FileGroupSize
END
CREATE TABLE ##FileGroupSize
(      DB VarChar(128)
    , FileGroup VarChar(128)
    , Used_MB Int
    , Free_MB Int
    , Size_MB Int
)
exec sp_MSforeachdb N'use [?]; 
INSERT INTO ##FileGroupSize
SELECT DB = db_name()
    , FileGroup = ISNULL(g.name, f.Type_Desc) 
    , Used_MB = SUM(FileProperty(f.name, ''SpaceUsed'')) / 128
    , Free_MB = SUM(f.size - FileProperty(f.name, ''SpaceUsed'')) / 128
    , Size_MB = SUM(f.size) / 128 
FROM sys.database_files f
    LEFT JOIN sys.filegroups g on f.data_space_id = g.data_space_id
WHERE f.Type_Desc = ''LOG''
GROUP BY f.Type_Desc, g.name
'
SELECT database_name = d.name 
    , d.create_date
    , d.compatibility_level
    , LastFullBU = BUFull.last_backup  
    , LastDiffBU = BUDiff.last_backup  
    , LastLogBU = BULog.last_backup  
    , Recovery_Model = d.recovery_model_desc
    , Log_Size_MB = LogSize.Size_MB
    , Log_Pct_Used = Cast(100 * Used_MB / (Cast(Size_MB as Dec(20,2)) + .01) as Dec(20,2))
    , d.collation_name
    , LastFullLoc = BUFullLoc.physical_device_name
    , LastFullSize_MB = Cast(BUFullLoc.backup_size / 1024 / 1024 as bigint)
    --, LastDiffLoc = BUDiffLoc.physical_device_name
    --, LastDiffSize_MB = Cast(BUDiffLog.backup_size / 1024 / 1024 as bigint)
    , LastLogLoc = BULogLoc.physical_device_name
    , LastLogSize_MB = Cast(BULogLoc.backup_size / 1024 / 1024 as bigint)
    , d.is_auto_close_on
FROM master.sys.databases d with (nolock)
    LEFT JOIN (SELECT b.database_name, max(b.backup_finish_date) last_backup
        FROM msdb..backupset b with (nolock)
        WHERE b.type = 'D' 
            AND (b.backup_finish_date > @cutoffdate or @use_cutoffdate = 0)
        GROUP BY b.database_name
        ) BUFull ON d.name = BUFull.database_name
LEFT JOIN (SELECT b.database_name, max(b.backup_finish_date) last_backup
        FROM msdb..backupset b with (nolock) 
        WHERE b.type = 'I' 
            AND (b.backup_finish_date > @cutoffdate or @use_cutoffdate = 0)
        GROUP BY b.database_name
        ) BUDiff ON d.name = BUDiff.database_name 
    LEFT JOIN (SELECT b.database_name, max(b.backup_finish_date) last_backup
        FROM msdb..backupset b with (nolock) 
        WHERE b.type = 'L' 
            AND (b.backup_finish_date > @cutoffdate or @use_cutoffdate = 0)
        GROUP BY b.database_name
        ) BULog ON d.name = BULog.database_name 
    LEFT JOIN (SELECT f.physical_device_name, b.database_name, b.backup_finish_date, b.backup_size
        FROM msdb..backupset b with (nolock)
            INNER JOIN msdb..backupmediafamily f ON b.media_set_id = f.media_set_id
        WHERE b.type = 'D') BUFullLoc ON BUFull.database_name = BUFullLoc.database_name AND BUFull.last_backup = BUFullLoc.backup_finish_date
    LEFT JOIN (SELECT f.physical_device_name, b.database_name, b.backup_finish_date, b.backup_size
        FROM msdb..backupset b with (nolock)
            INNER JOIN msdb..backupmediafamily f ON b.media_set_id = f.media_set_id
        WHERE b.type = 'I') BUDiffLoc ON BUDiff.database_name = BUDiffLoc.database_name AND BUDiff.last_backup = BUDiffLoc.backup_finish_date
    LEFT JOIN (SELECT f.physical_device_name, b.database_name, b.backup_finish_date, b.backup_size
        FROM msdb..backupset b with (nolock)
            INNER JOIN msdb..backupmediafamily f ON b.media_set_id = f.media_set_id 
        WHERE b.type = 'L') BULogLoc ON BULog.database_name = BULogLoc.database_name AND BULog.last_backup = BULogLoc.backup_finish_date
    LEFT JOIN ##FileGroupSize LogSize ON d.name = LogSize.DB
WHERE d.name <> 'tempdb'
    AND d.state = 0 --Online
DROP TABLE ##FileGroupSize