SQL Server Database Size and Free Disk Space reporting is critical in high volume databases.

One of the modelling applications I manage, maintains a large SQL Server database inventory on one SQL Server 2005 Enterprise Edition instance. The application generates a database per modelling request. Analysts make regular requests for jobs to model different modelling scenarios.

We’re talking about over 50 TB of production data spread across a number of drives. Archiving occurs regularly – moving data offline – readily available for restore.

As the drives can fill very quickly – we produce daily reports to users. Administrators make archiving decisions and manage job scheduling based on this report.

A SQL Server job emails a report every morning.The purpose of the report is to notify of:

Free Drive Space

Drive     |     MB_Free     |    GB_Free

Database Details

Database_Name     |    Database_Size_GB     |     Database _Size_MB    |     Drive_Location

To generate the recordsets three stored procedures are used:

1) Db_Details
2) manageDriveSpace
3) emailReportDatabaseDetails

1)Db_details

/*
NAME: Db_Details
PURPOSE:View details of all databases on this SQL server Instance:
Database_name, Database_size_kb,Database_size_mb,DBID,Filename
INPUT PARAMETERS:
EXAMPLE: Db_Details
NOTE:
*/
CREATE PROCEDURE [dbo].[Db_Details]
AS
BEGIN
    set nocount on
    declare @name sysname
    declare @SQL  nvarchar(600)

    /* Use temporary table to sum up database size w/o using group by */
    create table #databases (
                  DATABASE_ID int NOT NULL,
                  size decimal(38,2) NOT NULL)
    declare c1 cursor for
        select name from master.dbo.sysdatabases
            where has_dbaccess(name) = 1 -- Only look at databases to which we have access
    open c1
    fetch c1 into @name
    while @@fetch_status >= 0
    begin
        select @SQL = 'insert into #databases
                select '+ convert(sysname, db_id(@name)) + ', sum(size) from '
                + QuoteName(@name) + '.dbo.sysfiles'
        /* Insert row for each database */
        execute (@SQL)
        fetch c1 into @name
    end
    deallocate c1
    select 
        DATABASE_NAME = db_name(DATABASE_ID),
        DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */
        REMARKS = convert(varchar(254),null)    /* Remarks are NULL */
      into
            #database_list
    from #databases
    select 
            DATABASE_NAME,
            round(DATABASE_SIZE,2) as DATABASE_SIZE_KB,
            round((DATABASE_SIZE / 1024),2) as DATABASE_SIZE_MB,
            round(((DATABASE_SIZE / 1024)/1024),2) as DATABASE_SIZE_GB,
            DBID,
            FILENAME
       from
            #database_list
        inner join
           master..sysdatabases
      on
      database_name = name
END

2) manageDriveSpace

/*
NAME: manageDriveSpace
PURPOSE:Display free space on all Logical Disks attached to OS
The output will display : Drive,MB_Free
INPUT PARAMETERS :
EXAMPLE : manageDriveSpace
NOTE:
*/

CREATE PROCEDURE[dbo].[manageDriveSpace]
AS
SET NOCOUNT ON;

create table #FreeSpace(
 Drive char(1),
 MB_Free int)
insert into #FreeSpace exec xp_fixeddrives
select  Drive,MB_Free,MB_Free/1024 as 'GB_Free'  from #FreeSpace WHERE Drive IN ('E','F','H','I')
drop table #FreeSpace

2) emailReportDatabaseDetails

CREATE PROCEDURE emailReportDatabaseDetails
AS

--create temp table for databases
CREATE TABLE #EmailReportDatabases
(DATABASE_NAME VARCHAR(100),
DATABASE_SIZE_KB INT ,
DATABASE_SIZE_MB INT ,
DATABASE_SIZE_GB INT,
[DBID] INT,
[FILENAME] VARCHAR (200)
)

--create temp table fro drives
CREATE TABLE #EmailReportDriveFree
(DRIVE VARCHAR(1),
MB_Free INT,
GB_Free INT
)

--insert into staging table for databases
BEGIN
INSERT INTO #EmailReportDatabases
exec dbo.Db_Details
END

--display Drive free recordset
exec dbo.manageDriveSpace

--display database recordset
SELECT DATABASE_NAME,DATABASE_SIZE_GB,DATABASE_SIZE_MB,LEFT([FILENAME],1) AS DRIVE_LOCATION
FROM #EmailReportDatabases ORDER BY DRIVE_LOCATION ASC,DATABASE_SIZE_MB DESC
DROP TABLE #EmailReportDatabases
DROP TABLE #EmailReportDriveFree

SQL Server Database Mail

I use Database Mail to generate the report, attach as a file and send to the designated users.

Set up Database Mail on SQL Server 2005

SQL Server Database Size and Free Disk Space Report Sample

Beyond SQL Server Database Size and Free Disk Space reporting to users , I’ve set up monitoring to alert on thresholds. The monitoring scan the SQL Server every 5 minutes.DBAs receive the alerts.

Republished from http://www.sqlserver-dba.com.