SSRS Database Growth

I developed some reports to track database growth for both the file size and the used space inside of the database.  I feel like I am sharing my BEST ideas, in order to give back to the SQL community that has helped me so much.  You need to have intermediate skills to apply everything in this post.

Database: create Admin database

Create tables


CREATE TABLE [dba].[DatabaseSpace_Current](
[DatabaseSpaceId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[TotalDatabaseSizeMB] [numeric](8, 2) NULL,
[DataFileSizeMB] [numeric](8, 2) NULL,
[DataFileSpaceUsedMB] [numeric](8, 2) NULL,
[LogFileSizeMB] [numeric](8, 2) NULL,
[LogFileSpaceUsedMB] [numeric](8, 2) NULL,
[DateLogged] [date] NULL,
CONSTRAINT [PK_DatabaseSpaceC] PRIMARY KEY CLUSTERED ( [DatabaseSpaceId] )
)


CREATE TABLE [dba].[DatabaseSpace_Historical](
[DatabaseSpaceId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[TotalDatabaseSizeMB] [numeric](8, 2) NULL,
[DataFileSizeMB] [numeric](8, 2) NULL,
[DataFileSpaceUsedMB] [numeric](8, 2) NULL,
[LogFileSizeMB] [numeric](8, 2) NULL,
[LogFileSpaceUsedMB] [numeric](8, 2) NULL,
[DateLogged] [date] NULL,
CONSTRAINT [PK_DatabaseSpaceH] PRIMARY KEY CLUSTERED
( [DatabaseSpaceId] )
)

Create daily agent job with this step:


/**********************************************************************
Database Space Tracker
Author: Dan Gras
Date: 6/26/13
Description: Loads current stats on database disk space usage into
table dba.DatabaseSpace_Current, which is truncated first
Then loads this data into DatabaseSpace_Historical
***********************************************************************/

truncate table [dba].[DatabaseSpace_Current];

Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] int
)
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE ? SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') as spaceused from sys.sysfiles';

With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(

SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
INSERT INTO [dba].[DatabaseSpace_Current]
([DatabaseName]
,[TotalDatabaseSizeMB]
,[DataFileSizeMB]
,[DataFileSpaceUsedMB]
,[LogFileSizeMB]
,[LogFileSpaceUsedMB])
SELECT
SI.DBName,
CAST( D.DataFileSizeMB + L.LogFileSizeMB as numeric(8,2)) as TotalDatabaseSizeMB,
CAST( D.DataFileSizeMB as numeric(8,2)) as DataFileSizeMB,
CAST( D.DataFileSpaceUsedMB as numeric(8,2)) as DataFileSpaceUsedMB,
CAST( L.LogFileSizeMB as numeric(8,2)) as LogFileSizeMB,
CAST( L.LogFileSpaceUsedMB as numeric(8,2)) as LogFileSpaceUsedMB
FROM
SpaceInfo SI
CROSS APPLY (SELECT sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName) as D
CROSS APPLY (SELECT sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName) as L
WHERE DBName 'tempdb'
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB

INSERT INTO [dba].[DatabaseSpace_Historical]
([DatabaseName]
,[TotalDatabaseSizeMB]
,[DataFileSizeMB]
,[DataFileSpaceUsedMB]
,[LogFileSizeMB]
,[LogFileSpaceUsedMB]
,[DateLogged])
SELECT [DatabaseName]
,[TotalDatabaseSizeMB]
,[DataFileSizeMB]
,[DataFileSpaceUsedMB]
,[LogFileSizeMB]
,[LogFileSpaceUsedMB]
,[DateLogged]
FROM [dba].[DatabaseSpace_Current]

DatabaseGrowth report

Create a chart

Image

Image

Parameters

Parameter # 1 – DatabaseNameParam
Data Type: Text

Available values
Dataset: Database
Value field: DatabaseName
Label field: DatabaseName

Default values:
Dataset: Database
Value field: DatabaseName

Parameter #2 – TimeFrame
Data type: Text
Available values:
Label: Past Year Value: Year
Label: All Time Value: All
Label: Last 2 Years Value: Two

DataSet: DatabaseSizes


IF @TimeFrame = 'Two'
BEGIN

SELECT DatabaseName, DateLogged,
ISNULL(datafilespaceusedmb, DataFileSizeMB) AS DataFileUsedMB,
DataFileSizeMB AS DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_historical]
WHERE databaseName =@DatabaseNameParam
AND DateLogged >= Dateadd(month,-2, getdate() )
ORDER BY datelogged desc
END
ELSE
IF @TimeFrame = ‘Year’
BEGIN

SELECT DatabaseName, DateLogged,
ISNULL(datafilespaceusedmb, DataFileSizeMB) AS DataFileUsedMB,
DataFileSizeMB AS DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_historical]
WHERE databaseName =@DatabaseNameParam
AND DateLogged >= Dateadd(year,-1, getdate() )
ORDER BY datelogged desc

END
ELSE
BEGIN

SELECT DatabaseName,
DateLogged,
ISNULL(datafilespaceusedmb, DataFileSizeMB) AS DataFileUsedMB,
DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_historical]
WHERE databaseName =@DatabaseNameParam
ORDER BY datelogged desc

END

DataSet: Database

SELECT distinct [DatabaseName]
FROM [Admin].[dba].[DatabaseSpace_Current]
WHERE DataFileSizeMB > 1024.0

DatabaseGrowthEachDB report

Create table, with subreport in it.

Image

Image

Parameters

Parameter 1 – TimeFrame
Available values –

Label: Past Year.  Value: Year
Label: Last 2 Months.  Value: Two

DataSet Query:


IF @OrderBy = 'Name'
BEGIN

SELECT distinct [DatabaseName]
FROM [Admin].[dba].[DatabaseSpace_Current]
WHERE DataFileSizeMB > 1024.0

END
ELSE IF @OrderBy = ‘Percent’
BEGIN

SELECT distinct C.[DatabaseName], ABS ( ( C.DataFileSpaceUsedMB – ISNULL(H.DataFileSpaceUsedMB,0) ) / C.DataFileSpaceUsedMB )
FROM [Admin].[dba].[DatabaseSpace_Current] C
LEFT JOIN [Admin].dba.DatabaseSpace_Historical H
ON C.DatabaseName = H.DatabaseName AND H.DateLogged = DATEADD(MONTH, -1, C.DateLogged)
WHERE C.DataFileSizeMB > 1024.0
ORDER BY ABS ( ( C.DataFileSpaceUsedMB – ISNULL(H.DataFileSpaceUsedMB,0) ) / C.DataFileSpaceUsedMB ) desc

END
ELSE
BEGIN
SELECT distinct C.[DatabaseName], ABS( ( C.DataFileSpaceUsedMB – ISNULL(H.DataFileSpaceUsedMB,0) ) )
, c.DataFileSpaceUsedMB, h.DataFileSpaceUsedMB
FROM [Admin].[dba].[DatabaseSpace_Current] C
LEFT JOIN [Admin].dba.DatabaseSpace_Historical H
ON C.DatabaseName = H.DatabaseName AND H.DateLogged = DATEADD(MONTH, -1, C.DateLogged)
WHERE C.DataFileSizeMB > 1024.0
ORDER BY ABS( ( C.DataFileSpaceUsedMB – ISNULL(H.DataFileSpaceUsedMB,0) ) ) desc
END

ImageImageImage

Advertisements
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

One Response to SSRS Database Growth

  1. Pingback: SSRS database drive predictions | GlutenFree SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s