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
