One of my DBA tasks is to track the space used on our database servers. I want to know how big the database file sizes are, how big the tables are, and how much free space is available on each server. This is a common task for DBAs, and there’s probably a different method for each DBA. This is mine.

When I first started, I created a database on each production server to store all stored procedures, tables, and views that I use as a DBA. The tables are mostly repositories of server state at the time I run a query or procedure. In the case of database sizes I created the procedure GetDBFileStats to get the filesize, total space, free space, and space used for each file in each database: 

CREATE PROCEDURE [dbo].[GetDBFileStats]
AS

EXEC sp_msforeachdb 'USE [?]

CREATE TABLE #tmpspc (Fileid int, FileGroup int, TotalExtents int, UsedExtents int, Name sysname, FileName nchar(520))
 
INSERT #tmpspc EXEC (''DBCC SHOWFILESTATS'')
 
INSERT INTO PerfDB.dbo.dbStats            
SELECT GETDATE() AS RunDate,
    ''?'', 
    s.name AS [Name],
    s.physical_name AS [FileName],
    s.size * CONVERT(float,8) AS [Size],
    CAST(tspc.UsedExtents * CONVERT(float,64) AS float) AS [UsedSpace],
    (s.size * CONVERT(float,8) - CAST(tspc.UsedExtents*convert(float,64) AS float)) AS FreeSpace,
    s.file_id AS [ID]
FROM sys.filegroups AS g
    INNER JOIN sys.master_files AS s ON (s.type = 0 AND s.database_id = db_id() AND (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
    LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
ORDER BY [ID] ASC

DROP TABLE #tmpspc

INSERT INTO PerfDB.dbo.dbStats            
SELECT GETDATE() AS RunDate,
    ''?'', 
    s.name AS [Name],
    s.physical_name AS [FileName],
    s.size * CONVERT(float,8) AS [Size],
    CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float) * CONVERT(float,8) AS [UsedSpace],
    (s.size * CONVERT(float,8) - CAST(FILEPROPERTY(s.name, ''SpaceUsed'') AS float)* CONVERT(float,8)) AS FreeSpace,
    s.file_id AS [ID]
FROM sys.master_files AS s
WHERE (s.type = 1 AND s.database_id = db_id())
ORDER BY [ID] ASC'
 
GO

This was as far as I went until a few months ago. At that time I started to think of using SSIS for some of my daily tasks. So I created this simple package that gets the results from my procedure and puts them into a central database repository. I also truncate the tables and change the default date to just the date; 

Finally I created a report that got the data back. I send 2 parameters; a from date and a to date. Now when I run the report I can view what the database size was for the two dates and easily see how much the files have grown. I also created a sub report that, when I click on the database in the main report, opens to show the growth history of that database on a week to week basis.

Here’s the table I use to store the data and the procedure I use to populate the summary report. I also run it as an ad-hoc query;

CREATE TABLE [dbo].[dbStats](
    [Servername] [sysname] NOT NULL,
    [RunDate] [datetime] NOT NULL,
    [DBName] [sysname] NOT NULL,
    [Name] [sysname] NOT NULL,
    [FileName] [nchar](520) NOT NULL,
    [TotalSize] [float] NOT NULL,
    [UsedSpace] [float] NOT NULL,
    [FreeSpace] [float] NOT NULL,
    [FileID] [int] NOT NULL
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[prc_DBSizeCompare] (
    @minDate    DATETIME,
    @maxDate    DATETIME)
 
AS

SET NOCOUNT ON

;WITH DBCTE(ServerName, DBName, Name, MaxRunDate, MinRunDate) AS
(
 SELECT ServerName, DBName, Name, 
 MAX(RunDate) AS MaxRunDate,
 MIN(RunDate) AS MinRunDate
 FROM dbo.dbStats
 WHERE RunDate BETWEEN @minDate AND @maxDate
 AND DBName  'tempdb'
 GROUP BY ServerName, DBName, Name
)
SELECT db.Servername, db.DBName, db.Name, db.RunDate, db.TotalSize, db.UsedSpace, db.FreeSpace 
FROM dbo.dbStats db
 INNER JOIN DBCTE CTE ON db.Servername = CTE.ServerName
 AND db.Name = CTE.Name
WHERE db.RunDate = CTE.MinRunDate OR db.RunDate = CTE.MaxRunDate
ORDER BY db.Servername, db.DBName, db.FileID, db.RunDate

GO