SSRS database drive predictions

Another report that has been helpful in my DBA work is the database growth per disk drive report.  It shows the past database growth.  The most interesting thing though, is that it shows how much time you have left before the disk fills up.

Here is the final result, so that you can see if you want to build it or not.  Then there are details after the images.

Image

Image

Allright, the first thing you need to do is actually collect the data.  SSRS doesn’t even enter the scene at this point.  Plus, for the report to be interesting or accurate at all, you’ll need to collect the data for a month or so.

I will say that this may be best for smaller to mid-sized companies.  If you are in a huge company, you probably have a bunch of 3rd party tools anyway, so you won’t need a hand-crafted report.

First, build the dbo.DatabaseDrives table.

CREATE TABLE [dba].[DatabaseDrives](
[DatabaseName] [sysname] NOT NULL,
[DriveName] [varchar](10) NULL,
CONSTRAINT [PK_DatabaseDrives] PRIMARY KEY CLUSTERED ( [DatabaseName] )
)

Populate it with the database name, and the drive name of the MDF file.  Again, this works best in simple environments that have one data file per database

INSERT INTO [dba].[DatabaseDrives] ([DatabaseName],[DriveName])
VALUES (‘Database01′,’M’);

INSERT INTO [dba].[DatabaseDrives] ([DatabaseName],[DriveName])
VALUES (‘Database02′,’J’);

Then create the tables and jobs that are listed in my previous post:
https://glutenfreesql.wordpress.com/2013/11/20/ssrs-database-growth/

The tables and jobs include the following:

  • Table DatabaseSpace_Current
  • Table DatabaseSpace_Historical
  • Job with script “Database Space Tracker”

Okay, now we are ready for the SSRS.

Create a new report “DatabaseGrowthDrive.rdl”

Create a parameter named DriveName with a type of “text.” We will set up the available values later.

There are 5 datasets in this report, which is the most I’ve built in one report.  Some of the datasets have filters.

Image

Here are the details

DriveName:

SELECT distinct CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName
ELSE DatabaseSpace_Historical.DatabaseName
END as DriveName
FROM [Admin].[dba].[DatabaseSpace_Historical]
LEFT JOIN [Admin].dba.DatabaseDrives ON dba.DatabaseSpace_Historical.DatabaseName = dba.DatabaseDrives.DatabaseName
WHERE DateLogged >= ‘2013-6-1’
ORDER BY 1

DatabaseSizes_Current:

SELECT
CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName
ELSE DatabaseSpace_Current.DatabaseName
END as DriveName
, SUM( ISNULL(datafilespaceusedmb, DataFileSizeMB) ) AS DataFileUsedMB
, SUM( DataFileSizeMB) AS DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_Current]
LEFT JOIN [Admin].dba.DatabaseDrives ON dba.DatabaseSpace_Current.DatabaseName = dba.DatabaseDrives.DatabaseName
WHERE DateLogged >= ‘2013-6-1’
GROUP BY CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName
ELSE DatabaseSpace_Current.DatabaseName
END

Add a filter for the DriveName, as follows:

Image

DatabaseSizes_Historical:

SELECT
CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName
ELSE DatabaseSpace_Historical.DatabaseName
END as DriveName,
DateLogged,
SUM( ISNULL(datafilespaceusedmb, DataFileSizeMB) ) AS DataFileUsedMB,
SUM( DataFileSizeMB ) AS DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_historical]
LEFT JOIN [Admin].dba.DatabaseDrives ON dba.DatabaseSpace_Historical.DatabaseName = dba.DatabaseDrives.DatabaseName
WHERE DateLogged >= ‘2013-6-1’
GROUP BY
CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName ELSE DatabaseSpace_Historical.DatabaseName END,
DateLogged

Add a filter for the DriveName, as in image above.

MonthlyGrowth:

WITH HistoryByDrive AS(
SELECT
CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName ELSE DatabaseSpace_Historical.DatabaseName END as DriveName
,DateLogged
,SUM( ISNULL(datafilespaceusedmb, DataFileSizeMB) ) AS DataFileUsedMB
,SUM(DataFileSizeMB ) AS DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_historical]
LEFT JOIN [Admin].dba.DatabaseDrives ON dba.DatabaseSpace_Historical.DatabaseName = dba.DatabaseDrives.DatabaseName
WHERE DateLogged = DATEADD(day, -31,CAST( GETDATE() AS DATE))
AND DatabaseSpace_Historical.DatabaseName IN( SELECT databasename FROM dba.databasespace_current)
GROUP BY
CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName ELSE DatabaseSpace_Historical.DatabaseName END, DateLogged
),
CurrentDrive AS
(
SELECT
CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName ELSE DatabaseSpace_Current.DatabaseName END as DriveName
, SUM( ISNULL(datafilespaceusedmb, DataFileSizeMB) ) AS DataFileUsedMB
,SUM(DataFileSizeMB ) AS DataFileSizeMB
FROM [Admin].[dba].[DatabaseSpace_Current]
LEFT JOIN [Admin].dba.DatabaseDrives ON dba.DatabaseSpace_Current.DatabaseName = dba.DatabaseDrives.DatabaseName
GROUP BY CASE WHEN DatabaseDrives.DriveName IS NOT NULL THEN DatabaseDrives.DriveName ELSE DatabaseSpace_Current.DatabaseName END
)
SELECT C.driveName,
(C.DataFileUsedMB – H.DataFileUsedMB) AS MonthGrowthUsedMB,
(C.DataFileSizeMB – H.DataFileSizeMB) AS MonthGrowthFileSizeMB
FROM HistoryByDrive H
INNER JOIN CurrentDrive C ON H.driveName = C.drivename

Add a filter for the DriveName, as in image above.

Change the Available Values for the DriveName parameter:

-Change the Dataset to DriveName, the value field to DriveName, and the Label field to DriveName

Whew, now we can design the report:

Image

First, build the title text box.

Then in the second text box, is an expression showing the drive letter.
=Parameters!DriveName.Value & ” Drive”

Then create a chart pointing to the DatabaseSizes_Historical dataset.

In the Values box, reference the DataFileSizeMB and DataFileUsedMB columns.
In the Categories box, use the DateLogged column

Image

For the text box next to “Today’s date”, use the following expression:
=Today

For the text box next to “Last month growth:” use the following expressions:
=Sum(Fields!MonthGrowthFileSizeMB.Value, “MonthlyGrowth”) / 1024.0 GB File Growth
=Sum(Fields!MonthGrowthUsedMB.Value, “MonthlyGrowth”) / 1024.0 GB Used

For the text box next to “Drive capacity”, simply type the number of GB you have on that disk.

For the text box next to “Drive space used today”, use the following expression:
=Sum(Fields!DataFileSizeMB.Value, “DatabaseSizes_Current”) / 1024 GB

For the text box next to “Drive percentage full as of today”, use the following expression:
=Sum(Fields!DataFileSizeMB.Value, “DatabaseSizes_Current”) / ( 250.0 * 1024 )

For the text box next to “Projected date 80% full”, use the following expression.  The 250 referenced will vary on your system, since it’s the capacity of the drive.  I should probably make that database driven.
=
DateAdd(
DateInterval.Month,
( (250 * 1024 * 0.8) – Sum(Fields!DataFileSizeMB.Value, “DatabaseSizes_Current”) )
/ Sum(Fields!MonthGrowthUsedMB.Value, “MonthlyGrowth”)
, Today( )
)

For the text box next to “Projected date 90% full”, use the following expression.  The 250 referenced will vary on your system, since it’s the capacity of the drive.  I should probably make that database driven.
=
DateAdd(
DateInterval.Month,
( (250 * 1024 * 0.9) – Sum(Fields!DataFileSizeMB.Value, “DatabaseSizes_Current”) )
/ Sum(Fields!MonthGrowthUsedMB.Value, “MonthlyGrowth”)
, Today( )
)

For the text box next to “Projected date 100% full”, use the following expression.  The 250 referenced will vary on your system, since it’s the capacity of the drive.  I should probably make that database driven.
=
DateAdd(
DateInterval.Month,
( (250 * 1024 * 1.0) – Sum(Fields!DataFileSizeMB.Value, “DatabaseSizes_Current”) )
/ Sum(Fields!MonthGrowthUsedMB.Value, “MonthlyGrowth”)
, Today( )
)

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to SSRS database drive predictions

  1. Ser says:

    This is very helpful. Thank you! I am working in a mid level sized company but most of my databases have filegroups..is there a way to build this report when there are more than one fg?

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