Database Space Capacity Planning

Database and volume space capacity planning is one of the core tasks of database administrator. In order accomplish space capacity planning we must exam both the volume or disk level as well as database space usage over a period time. The goal of database space capacity is first to avoid running out of space and second to maintain an acceptable level of slack or free space.

On a volume basis, the NTFS file system requires a certain amount of free space to operate effectively and to facilitate defragmentation, generally 20% free space is often mentioned. At the database level the amount of free space needed will vary by database the more important consideration is to avoid auto growth. The main reasons given for proactively managing database space are avoiding the performance impact encountered should a transaction trigger database growth and reducing physical NTFS fragmentation of the underlying database files caused by file growth. The use of auto grow is viewed as safety mechanism and not as a replacement for space capacity planning.

There are many articles and blog posts on the subject of space capacity planning, one noteworthy post from Microsoft Operations describes a report, which focuses on "days remaining." When dealing with thousands of databases over hundreds of servers focusing on a "days remaining" metric allows the database administrator to see the specific databases and volumes that need attention. The post from Microsoft Operations doesn't provide a complete soluton, for instance the code to gather volume space, data collection, and reports are not provided. In this article we look at a complete solution using a few queries, PowerShell and SSRS to produce a consolidated database and volume space forecasting report that focuses on "days remaining" metric

Getting Started

Overview

1. Create the tables using spacedm.tables.sql
2. Populate the server_space_lku with the list of SQL instance to report against
3. Create the views using spacedm.views.sql
4. Create the procedures using spacedm.procedures.sql
5. Run/Schedule the database space data collection PowerShell script Write-DbSpaceToDb.ps1
6. Run/Schedule the volume space data collection PowerShell script, Write-VolSpaceToDb.ps1
7. Optionally deploy SQL Server Reporting Services (2008) reports

The download section of this article includes several SQL scripts needed for the consolidated reports. To get started, create a database in this example we'll use spacedm. Then run the code in spacedm.tables.sql to create the following tables.

Add SQL instances to the server_space_lku for each instance you wish to collect space information. For example the following code adds the Z001\Sql1 server:

insert server_space_lku (server_name) values ('Z001\Sql1')

Next create the following views in the spacedm database by running the spacedm.views.sql script in the accompanying download:

Finally, run the spacedm.procedures.sql script from the accompanying download to create the following procedures:

The data will be collected by the Powershell scripts, Write-DbSpaceToDb.ps1 and Write-VolToDb.ps1.

Database Space (Write-DbSpaceToDb.ps1)

To capture database space we will use the following PowerShell script, Write-DbSpaceToDb.ps1, which is also included in the download section. The script iterates through a list of SQL instances in our server_space_lku tables and executes a file/filegroup space usage query for all databases for each instance. The data is then bulk inserted into the db_space table using the .NET Data.SqlClient.SqlBulkCopy class. Note: this has been tested against SQL Server 2000, 2005, and 2008 servers:

param($destServer, $destdb)

#######################
function Get-SqlData

{
param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'),[string]$query=$(throw 'query is required.'))

Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)
$dt = New-Object "System.Data.DataTable"
[void]$da.fill($dt)

$dt

} #Get-SqlData

#######################
function Get-DbSpace

{
param($srcServer)

$qry = @"
CREATE TABLE #output(

server_name varchar(128),

dbname varchar(128),

physical_name varchar(260),

dt datetime,

file_group_name varchar(128),

size_mb int,

free_mb int)

exec sp_MSforeachdb @command1=
'INSERT #output

SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS server_name,

''?'' AS dbname,

f.filename AS physical_name,

CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,

g.groupname,

CAST (size*8.0/1024.0 AS int) AS ''size_mb'',

CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int) AS ''free_mb''

FROM ?..sysfiles f

JOIN ?..sysfilegroups g

ON f.groupid = g.groupid'

SELECT * FROM #output
"@
Get-SqlData $srcServer 'master' $qry

}# Get-DbSpace

#######################
function Write-DataTableToDatabase

{
param ($dataTable,$destTbl)

$connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy.DestinationTableName = "$destTbl"
$bulkCopy.WriteToServer($dataTable)

}# Write-DataTableToDatabase

#######################
## MAIN ##

#######################

Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku" |
foreach {

$dataTable = Get-DBSpace $_.server_name
if ($dataTable)

{ Write-DataTableToDatabase $dataTable 'db_space' }
}

Volume/Disk Space (Write-VolToDb.ps1)

The PowerShell script Write-VolTodb.ps1 makes quick work at collecting WMI volume information. Like the database space script, loops through the list of SQL Servers, but this time we will use the Get-WmiObject cmdlet to collect volume information and calculate a few new properties. In order to load the data into a SQL Server table, a function called Out-DataTable converts the output into a DataTable. This is the same function described in a previous article, Loading Data With Powershell. The function is adapted from code by Marc van Orsouw (/\/\o\/\/), The PowerShell Guy, in this blog post PowerShell GUI ScripBlock Monitor Script. The additional step of converting the output to a DataTable is necessary since the Data.SqlClient.SqlBulkCopy class we will use to load the data into a SQL table expects either a DataTable or an array of DataRows as an input parameter. If you're dealing with data obtained through a SQL query and ADO.NET, such as our database space script, the output is already in a DataTable or array of DataRow's format.

param($destServer, $destDb)

#######################

function Get-SqlData

{

param([string]$serverName=$(throw 'serverName is required.'), [string]$databaseName=$(throw 'databaseName is required.'), [string]$query=$(throw 'query is required.'))

Write-Verbose "Get-SqlData serverName:$serverName databaseName:$databaseName query:$query"

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString)

$dt = New-Object "System.Data.DataTable"

[void]$da.fill($dt)

$dt

} #Get-SqlData

#######################

function Get-Vol

{

param($computerName)

Get-WmiObject -computername "$ComputerName" Win32_Volume -filter "DriveType=3" |

foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))

add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Capacity/1GB),2))

add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))

add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Capacity) * 100),2)) -passThru} |

select UsageDT, SystemName, Name, Label, SizeGB, FreeGB, PercentFree

}# Get-Vol

#######################
## The Out-DataTable function Adapted from http://thepowershellguy.com/blogs/posh/archive/2007/01/21/powershell-gui-scripblock-monitor-script.aspx
#######################

function Out-DataTable

{

param($Properties="*")

Begin

{

$dt = new-object Data.datatable

$First = $true

}

Process

{

$DR = $DT.NewRow()

foreach ($item in $_ | Get-Member -type *Property $Properties ) {

$name = $item.Name

if ($first) {

$Col = new-object Data.DataColumn

$Col.ColumnName = $name

$DT.Columns.Add($Col)

}

$DR.Item($name) = $_.$name

}

$DT.Rows.Add($DR)

$First = $false

}

End

{

return @(,($dt))

}

}# Out-DataTable

#######################

function Write-DataTableToDatabase

{

param($destServer,$destDb,$destTbl,$dt)

$connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

$bulkCopy.DestinationTableName = "$destTbl"

$bulkCopy.WriteToServer($dt)

}# Write-DataTableToDatabase

#######################

Get-SqlData $destServer $destDb "SELECT server_name FROM server_space_lku" |

foreach {
#Get just the server name portion if instance name is included

$computerName = $_.server_name -replace "\\.*",""

$dt = Get-Vol $computerName | Out-DataTable

Write-DataTableToDatabase $destServer $destDb 'vol_space' $dt

}

In order for database and volume space to be useful you should schedule a daily data collection process. A SQL Agent job creation script, spacedm.job.sql is included in the download section.

Queries

After collecting the data over a period of time you can calculate a daily growth rate by self joining the tables to the previous day's value. For example the following query excerpted from the db_space_change_vw view returns a daily growth amount by day. We will use this in subsequent queries to determine our "days remaining" metric:

SELECT d1.server_name, d1.dbname, d1.physical_name, d1.dt, d1.size_mb, d1.free_mb,
ROUND(CAST(d1.free_mb AS float)/d1.size_mb * 100,2) AS percent_free,
(d1.size_mb - d1.free_mb) AS allocate_mb,
(d1.size_mb - d1.free_mb) - (d2.size_mb -d2.free_mb) AS daily_growth_mb
FROM dbo.db_space d1
JOIN dbo.db_space d2
ON d1.server_name = d2.server_name
AND d1.dbname = d2.dbname
AND d1.physical_name = d2.physical_name
AND d2.dt = DATEADD(dd,-1,d1.dt)
WHERE d1.size_mb > 0

We can then make use of a couple CTEs and the db_space_change_vw to return an average, beginning, ending and days remaining values:

CREATE PROCEDURE [dbo].[db_space_capacity_sp]
@beginDt datetime, @endDt datetime
AS

SET NOCOUNT ON

;WITH 
avg_db_space (server_name, dbname, physical_name, avg_growth_mb)
AS
(
SELECT server_name, dbname, physical_name, ROUND(AVG(daily_growth_mb),2)
FROM db_space_change_vw
WHERE dt BETWEEN @beginDt AND @endDt
GROUP BY server_name, dbname, physical_name 

),
begin_db_space (server_name, dbname, physical_name, begin_dt, begin_size_mb, begin_free_mb, begin_percent_free)
AS
(
SELECT server_name, dbname, physical_name, dt, size_mb, free_mb, percent_free
FROM dbo.db_space_change_vw
WHERE dt = @beginDt
)
SELECT e.server_name, e.dbname, e.physical_name, begin_dt, e.dt AS end_dt, begin_size_mb,
e.size_mb AS end_size_mb, begin_free_mb, e.free_mb AS end_free_mb, begin_percent_free,
e.percent_free AS end_percent_free, (e.size_mb - e.free_mb) AS allocated_mb, avg_growth_mb, 
CASE
WHEN avg_growth_mb > 0 THEN 
CAST(ROUND(e.free_mb / avg_growth_mb,2) AS numeric(18,2))
ELSE
NULL
END AS days_remaining
FROM db_space_change_vw e
JOIN avg_db_space a
ON e.server_name = a.server_name
AND e.dbname = a.dbname
AND e.physical_name = a.physical_name
JOIN begin_db_space b
ON e.server_name = b.server_name
AND e.dbname = b.dbname
AND e.physical_name = b.physical_name
WHERE e.dt = @endDt
ORDER BY e.server_name, e.dbname, e.physical_name

The daily growth rate for volume information is calculated in much the same manner. The following query calculates a daily growth rate (excerpt from vol_space_change_vw):

SELECT v1.server_name, v1.vol_name, v1.dt, v1.vol_lbl, v1.size_gb, v1.free_gb, v1.percent_free,
(v1.size_gb * .8) AS usable_size_gb, (v1.size_gb - v1.free_gb) AS allocated_gb,
(v1.size_gb - v1.free_gb) - (v2.size_gb -v2.free_gb) AS daily_growth_gb
FROM dbo.vol_space v1
JOIN dbo.vol_space v2
ON v1.server_name = v2.server_name
AND v1.vol_name = v2.vol_name
AND v2.dt = DATEADD(dd,-1,v1.dt)

And finally the volume procedure returns average, beginning, ending and days remaining values for volume data:

CREATE PROCEDURE [dbo].[vol_space_capacity_sp]
@beginDt datetime, @endDt datetime
AS

SET NOCOUNT ON

;WITH 
avg_vol_space (server_name, vol_name, avg_growth_gb)
AS
(
SELECT server_name, vol_name, ROUND(AVG(daily_growth_gb),2)
FROM vol_space_change_vw
WHERE dt BETWEEN @beginDt AND @endDt
GROUP BY server_name, vol_name

),
begin_vol_space (server_name, vol_name, begin_dt, begin_size_gb, begin_free_gb, begin_percent_free)
AS
(
SELECT v.server_name, v.vol_name, v.dt, v.size_gb, v.free_gb, v.percent_free
FROM dbo.vol_space v
JOIN (SELECT server_name, vol_name, min(dt) AS dt FROM dbo.vol_space 
WHERE dt BETWEEN @beginDt AND @endDt GROUP BY server_name, vol_name) AS m
ON v.server_name = m.server_name
AND v.vol_name = m.vol_name
AND v.dt = m.dt
) 
SELECT e.server_name, e.vol_name, begin_dt, e.dt AS end_dt, e.vol_lbl, begin_size_gb,
e.size_gb AS end_size_gb, begin_free_gb, e.free_gb AS end_free_gb, begin_percent_free,
e.percent_free AS end_percent_free, usable_size_gb, allocated_gb, avg_growth_gb, 
CASE
WHEN avg_growth_gb > 0 THEN 
CAST(ROUND((e.size_gb - e.allocated_gb)/ avg_growth_gb,2) AS numeric(18,2))
ELSE
NULL
END AS days_remaining
FROM vol_space_change_vw e
JOIN avg_vol_space a
ON e.server_name = a.server_name
AND e.vol_name = a.vol_name
JOIN begin_vol_space b
ON e.server_name = b.server_name
AND e.vol_name = b.vol_name
WHERE e.dt = @endDt
ORDER BY e.server_name, e.vol_name

Reporting

The following reports are included in the download section of the article:

To configure the reports open the Solution file, SpaceAnalysis.sln in Visual Studio 2008 (VS) or SQL Server Business Intelligence Studio (BIDS) 2008 and change the shared data source to your server and database name. Having completed the setup, data collection and report configuration you can generate the reports. You can run the reports locally through VS or BIDS or deploy the reports to a SQL Server Reporting Services 2008 Server. The following are several sample reports

Database Capacity

Volume Capacity

Summary

The need to monitor and forecast database and volume space is a critical task for database administrators. You can use the process described in this article to create a consolidated space forecasting report, which focuses on a "days remaining" metric. In addition, the use of PowerShell to collect data and load into a SQL table as demonstrated in this article, provides a solution you can easily adapt to many database administration problems.