Check Disk Space script

Part of normal maintenance of a Dynamics GP SQL server is to make sure there is enough disk space for your data as it grows. Similarly, you need to make sure your data files aren't growing more than they should.

This script draws from several system tables to show the recovery model, the last backup of the data or log file, the size of the file, the growth of the file the next time it runs out of room, the drive on which the file is stored, and the free space of the drive.

I included the Recovery Model and LastSQLBackup columns to show the situation where FULL recovery is used, but no Transaction Log backups are taking place. This situation causes the Transaction Log to grow unnecessarily until it fills the hard drive. If you do not back up the Transaction Log, having FULL recovery is both useless and dangerous to your data at the same time; dangerous because it can bottom out your hard drive while transactions are being posted, and useless because the log file hasn't been backed up to be able to use in a restore which is the whole point of having it in the first place.

-- Shows database file space usage and free disk space
USE [master]

IF EXISTS (SELECT * FROM sys.tables WHERE name='#cbs_drivespace')
    DROP TABLE #cbs_drivespace

IF EXISTS (SELECT * FROM sys.tables WHERE name='##cbs_db_sizes')
    DROP TABLE ##cbs_db_sizes

CREATE TABLE #cbs_drivespace
(
    Drive varchar(10), 
    MB_Free numeric(19,2)
)

CREATE TABLE ##cbs_db_sizes
(
    dbname varchar(100),
    filetype varchar(4),
    size_in_gb numeric(19,2),
    growth_in_percent numeric(19,2),
    growth_in_gb numeric(19,2)
)

DECLARE @SQL varchar(4000)
declare @DatabaseId varchar(255)

INSERT INTO #cbs_drivespace(Drive, MB_Free)
EXEC master.dbo.xp_fixeddrives

declare DatabaseLoop cursor for 
    select name from master..sysdatabases where name in 
        (select INTERID from DYNAMICS..SY01500 union select 'DYNAMICS')
        or name in ('master','msdb','tempdb')

open DatabaseLoop
fetch next from DatabaseLoop into @DatabaseId
while (@@fetch_status <> -1)
    begin

    /*Inner Loop for each Database*/
    SET @SQL =
        'INSERT INTO ##cbs_db_sizes '
        + 'SELECT ''' + @DatabaseId + ''' AS dbname, '
        + 'CASE WHEN RIGHT(RTRIM(filename),3) = ''mdf'' THEN ''Data'' ELSE ''Log'' END AS filetype, '
        + 'CONVERT(numeric(19,2),size * 8 / 1024. / 1024) AS size_in_gb, '
        + 'CONVERT(numeric(19,2),CASE WHEN status & 0x100000 > 0 THEN growth ELSE (growth * 100.) / (size * 1.) END) AS growth_in_percent, '
        + 'CONVERT(numeric(19,2),CASE WHEN status & 0x100000 = 0 THEN growth * 8 / 1024. / 1024 ELSE size * (growth / 100.) * 8 / 1024 / 1024 END) AS growth_in_gb '
        + 'FROM ' + @DatabaseId + '..sysfiles'
    EXEC (@SQL)
    fetch next from DatabaseLoop into @DatabaseId 
    end

close DatabaseLoop
deallocate DatabaseLoop

SELECT
    db.name as DBName,
    db.dbid as DBID,
    DatabasePropertyEX(db.name, 'Recovery') as RecoveryModel,
    s.filetype as FileType,
    CASE 
        WHEN s.filetype = 'Data' 
            THEN ISNULL(CONVERT(varchar(15),bu.LastBU,101),'Never')
        WHEN s.filetype = 'Log' AND DatabasePropertyEX(db.name, 'Recovery') = 'SIMPLE'
            THEN 'N/A'
        ELSE
            ISNULL(CONVERT(varchar(15),bu.LastBU,101),'Never')
    END LastSQLBackup,
    s.size_in_gb as SizeInGB,
    s.growth_in_gb as GrowthInGB,
    s.growth_in_percent as GrowthInPercent,
    ds.Drive as DriveLetter,
    CONVERT(numeric(19,2),ds.MB_Free / 1024) DriveGBFree
FROM
    sysdatabases db
INNER JOIN
    ##cbs_db_sizes s
    ON db.name = s.dbname
INNER JOIN
    #cbs_drivespace ds
    ON LEFT(db.filename,1) = ds.Drive
LEFT JOIN
    (
        SELECT
            a.Name as dbname,
            COALESCE(Convert(varchar(100), MAX(b.backup_finish_date), 101),'Backup Not Taken') as LastBU,
            b.type as BUType,
            CASE 
                WHEN b.type = 'D' THEN 'Data'
                WHEN b.type = 'L' THEN 'Log'
            END as BUFileMatch
        FROM 
            sysdatabases a 
        LEFT OUTER JOIN 
            msdb.dbo.backupset b
            ON b.database_name = a.name
        WHERE
            b.type IN ('D','L')
        GROUP BY 
            a.Name,
            b.type
    ) bu
    ON s.dbname = bu.dbname
    AND s.filetype = bu.BUFileMatch
ORDER BY
    DBName,FileType

DROP TABLE #cbs_drivespace
DROP TABLE ##cbs_db_sizes