So, as I’ve mentioned in the past, I have an inventory database where I store information on our various SQL Server installations. It has instance data (versions, editions, patch levels, collation settings, etc), database information (compatibility level, creation date, collation, recovery model, the backup location, etc.), and more. I also use it to track database growth, which comes in handy right around this time every year. It’s budget season once again, and the most common question I’m going to be asked is: “How are we on disk space for [insert database/application name here]?”
I don’t know about you, but I don’t really like answering the same question over and over. So this year I decided to make it more self-service. I wanted managers to have access to a report that would show them the current total disk usage on an instance, the growth rate over the past year, and the projected usage 1 year from now based on that rate. But that doesn’t tell them whether or not they have enough disk space for that growth, so I needed to include mount point information. And because some instances are shared by several applications, I also needed to break out the disk usage by individual database. If one application is using the lion’s share of disk, they need to foot the bill accordingly.
So I started in my SS_Inventory database. I use 4 tables for this process:
- Servers is the master list of all my instances
- Database_Files stores the most recent datafile information for all instances
- Database_Growth stores historical datafile size information
- Mount_Points stores the most recent information on the mount points for all instances.
USE [SS_Inventory] GO CREATE TABLE [dbo].[Servers]( [instance_name] [varchar](40) NULL, [instance_name_short] [varchar](40) NULL, [server_name] [varchar](40) NULL, [server_name_short] [varchar](40) NULL, [Environment] [varchar](7) NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Database_Files]( [instance_name] [nvarchar](128) NULL, [database_name] [nvarchar](128) NULL, [file_id] [int] NOT NULL, [type_desc] [nvarchar](60) NULL, [name] [sysname] NOT NULL, [physical_name] [nvarchar](260) NOT NULL, [size_mb] [int] NULL, [growth_percent] [int] NOT NULL, [growth_mb] [int] NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Database_Growth]( [instance_name] [nvarchar](128) NULL, [database_name] [nvarchar](128) NULL, [datafile_mb] [int] NULL, [logfile_mb] [int] NULL, [run_date] [date] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Mount_Points]( [server_name] [nvarchar](128) NULL, [mount_point] [varchar](256) NULL, [UsedSpaceMB] [int] NULL, [FreeSpaceMB] [int] NULL, [TotalSpaceMB] [int] NULL, [PercFree] [decimal](5, 2) NULL ) ON [PRIMARY] GO
The information in the Database_Files and Mount_Points tables is populated once a week by a couple of Powershell scripts. I’ll get to those in a minute. The Database_Growth table is populated via a simple stored procedure after Database_Files is loaded.
CREATE PROCEDURE [dbo].[usp_StoreDBGrowth] as INSERT INTO Database_Growth SELECT instance_name, database_name, SUM(case when type_desc = 'ROWS' then size_mb else 0 end) as datafile_mb , SUM(case when type_desc = 'LOG' then size_mb else 0 end) as logfile_mb , CAST(getdate() as DATE) as run_date FROM SS_Inventory.dbo.Database_Files GROUP BY instance_name, database_name GO
Ok, so now that we’ve got our infrastructure in place, let’s get some data in there. (Actually, I’ve been storing the Database_Growth information for quite some time, over 3 years, so if you’re just implementing this, it’ll take some time before you can really trend growth.) Previously I’d been gathering this data using SSIS. I hated it. I don’t have enough knowledge about SSIS to really know what I’m doing and whether what I’m doing is the best way. It worked, but it was a pain in the ass to modify and debug and I’d just had enough. I wanted to rewrite it in a way that would be simple, easy to modify, and fast. I chose Powershell. Now I’m not claiming to be some Powershell expert. Far from it. But the big thing it has going for it is all the sample code out there on the intertubes. Seriously, I barely had to write a thing from scratch, and why reinvent the wheel?
Anyway, the first script I needed was something to get the mount point data. I found a good starting point that only needed a couple tweaks for my purposes.
# Get-MountPointInfo.PS1 Script
#
# Original script by:
# Eric Woodford
# Scripts@ericwoodford.com
# Nov 11, 2008
# Discover and detail volume mount points on a specified Windows server.
#
# Customized by:
# Colleen M. Morrow
# http://colleenmorrow.com
# July 13, 2011
# Changes to original:
# Corrected issue with incorrect used space (not accounting for subfolders)
# Use win32_valume capacity for total size
# Add external loop through servers to inventory
# Direct output to table, rather than file
#
#
function Get-MountPointInfo($Target) {
$Summary = @()
$objFSO = New-Object -com Scripting.FileSystemObject
$MountPoints = gwmi -class "win32_mountpoint" -namespace "root\cimv2" -computername $Target
$Volumes = gwmi -class "win32_volume" -namespace "root/cimv2" -ComputerName $Target| select name, freespace, capacity
foreach ($MP in $Mountpoints) {
$MP.directory = $MP.directory.replace("\\","\")
foreach ($v in $Volumes) {
$vshort = $v.name.Substring(0,$v.name.length-1 )
$vshort = """$vshort""" #Make it look like format in $MP (line 11).
if ($mp.directory.contains($vshort)) { #only queries mountpoints that exist as drive volumes no system
$Record = new-Object -typename System.Object
$usedspace = $v.capacity-$v.freespace
$freespace = $v.freespace
$capacity = $v.capacity
$percFree = ( $v.freespace/$v.capacity)*100
$mountpoint = $v.name
$connection2 = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection2.Open()
$cmd2 = $connection2.CreateCommand()
$cmd2.CommandText = "Insert into $ssi_table values ('$Target', '$mountpoint', $usedspace/(1024*1024), $freespace/(1024*1024), $capacity/(1024*1024), $percFree)"
$null = $cmd2.ExecuteNonQuery()
}
}
}
}
And I wrote a separate script that loops through the servers in my Servers table and calls Get-MountPointInfo() for each one.
# Mount_Points.ps1
# Colleen M. Morrow
# http://colleenmorrow.com
# usage: ./Mount_Points.ps1 "[Inventory instance]" "[inventory login]" "[inventory password]"
# Load Mount_Points table in SS_Inventory database
param(
[string]$ssi_instance=$null,
[string]$ssi_userid=$null,
[string]$ssi_passwd=$null
)
. .\write-datatable.ps1
. .\Get-MountPointInfo.ps1
$ssi_database = "SS_Inventory"
$ssi_table = "Mount_Points"
$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### First clear out Mount_Points #####
$query = "TRUNCATE TABLE $ssi_table"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
##### Get the list of Servers to inventory #####
$query = "SELECT DISTINCT server_name_short as Server FROM Servers WHERE Environment = 'PROD'"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
while($reader.Read()) {
$Target = $reader['Server']
Get-MountPointInfo($Target)
}
So there’s my mount point information. Now what about the datafile and growth information? Well, again I found a function online that handles writing data to a database table.
#######################
<#
.SYNOPSIS
Writes data only to SQL Server tables.
.DESCRIPTION
Writes data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.
.INPUTS
None
You cannot pipe objects to Write-DataTable
.OUTPUTS
None
Produces no output
.EXAMPLE
$dt = Invoke-Sqlcmd2 -ServerInstance "Z003\R2" -Database pubs "select * from authors"
Write-DataTable -ServerInstance "Z003\R2" -Database pubscopy -TableName authors -Data $dt
This example loads a variable dt of type DataTable from query and write the datatable to another database
.NOTES
Write-DataTable uses the SqlBulkCopy class see links for additional information on this class.
Version History
v1.0 - Chad Miller - Initial release
v1.1 - Chad Miller - Fixed error message
.LINK
http://msdn.microsoft.com/en-us/library/30c3y597%28v=VS.90%29.aspx
#>
function Write-DataTable
{
[CmdletBinding()]
param(
[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
[Parameter(Position=1, Mandatory=$true)] [string]$Database,
[Parameter(Position=2, Mandatory=$true)] [string]$TableName,
[Parameter(Position=3, Mandatory=$true)] $Data,
[Parameter(Position=4, Mandatory=$false)] [string]$Username,
[Parameter(Position=5, Mandatory=$false)] [string]$Password,
[Parameter(Position=6, Mandatory=$false)] [Int32]$BatchSize=50000,
[Parameter(Position=7, Mandatory=$false)] [Int32]$QueryTimeout=0,
[Parameter(Position=8, Mandatory=$false)] [Int32]$ConnectionTimeout=15
)
$conn=new-object System.Data.SqlClient.SQLConnection
if ($Username)
{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
else
{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
$conn.ConnectionString=$ConnectionString
try
{
$conn.Open()
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.BatchSize = $BatchSize
$bulkCopy.BulkCopyTimeout = $QueryTimeOut
$bulkCopy.WriteToServer($Data)
$conn.Close()
}
catch
{
$ex = $_.Exception
Write-Error "$ex.Message"
continue
}
} #Write-DataTable
Now I needed a wrapper script. This one is very similar to the mount point script in that it gets a list of servers from the Servers table and loops through them. I’ve documented it quite heavily, I think, so it should be fairly self-explanatory.
# Inventory_Datafiles.ps1
# usage: ./Inventory_Datafiles.ps1 "<Inventory Instance name>" "<inventory login>" "<inventory password>"
# Collects datafile Inventory information into SS_Inventory database
param(
[string]$ssi_instance=$null,
[string]$ssi_userid=$null,
[string]$ssi_passwd=$null
)
. .\write-datatable.ps1
$ssi_database = "SS_Inventory"
$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
##### Clean out tables pre-run #####
$query = "TRUNCATE TABLE Database_Files;"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
##### Get the list of servers to inventory #####
$query = "SELECT DISTINCT instance_name as Server FROM Servers WHERE Environment IN ('PROD')"
$cmd.CommandText = $query
$reader = $cmd.ExecuteReader()
##### For every server gather data #####
while($reader.Read()) {
$svr = $reader['Server']
$conn = "server=$svr;database=master;User ID=$ssi_userid;password=$ssi_passwd"
##### Database Files #####
$ssi_table = "Database_Files"
$query = "select @@SERVERNAME as instance_name, DB_Name(database_id) as database_name, file_id, type_desc, name, physical_name, (size)*8/1024 as size_mb
,case (is_percent_growth) WHEN 1 THEN growth ELSE 0 END as growth_percent
,case (is_percent_growth) WHEN 0 THEN growth*8/1024 ELSE 0 END as growth_mb
from sys.master_files
WHERE type in (0, 1)"
$da = new-object System.Data.SqlClient.SqlDataAdapter ($query, $conn)
$dt = new-object System.Data.DataTable
$da.fill($dt) | out-null
Write-DataTable -ServerInstance $ssi_instance -Database $ssi_database -TableName $ssi_table -Data $dt -Username $ssi_userid -Password $ssi_passwd
}
##### Execute usp_StoreDBGrowth procedure #####
$connection = new-object system.data.sqlclient.sqlconnection( `
"Data Source=$ssi_instance;Initial Catalog=$ssi_database;User Id=$ssi_userid; Password=$ssi_passwd;");
$connection.Open()
$cmd = $connection.CreateCommand()
$query = "exec usp_StoreDBGrowth"
$cmd.CommandText = $query
$null = $cmd.ExecuteNonQuery()
Still with me? Awesome! So we have our tables, and we have a way to populate them, and we let things run for a month or three, until we’ve gathered enough historical data to do some growth projections. (If this were a cooking show, this is the point where I put the cake pan in the oven and whip out a fully baked cake from under the counter. mmmm, cake.) I’m not going to walk you through creating an SSRS report. There are plenty of blogs out there dedicated to SSRS. What I’ll show you is the stored procedure that my SSRS report calls. First the code.
USE [SS_Inventory]
GO
CREATE procedure [dbo].[usp_ProjectedDBGrowth](@instance_name VARCHAR(128))
AS
BEGIN
DECLARE @instance_part VARCHAR(128)
DECLARE @server_name VARCHAR(128)
DECLARE @database_name VARCHAR(256)
DECLARE @max_date date
DECLARE @min_date date
DECLARE @currsize DECIMAL(12,2)
DECLARE @prevsize DECIMAL(12,2)
DECLARE @avgmongrow DECIMAL(12,2)
DECLARE @prevyrgrow DECIMAL(12,2)
DECLARE @totalpctgrow DECIMAL(12,5)
BEGIN TRY
IF @instance_name IS NULL
RAISERROR ('Instance name cannot be null. Usage: usp_ProjectedDBGrowth ''N1DB35MS\N1DB35MS'' ', 16, 1);
/* Break down the instance name into server and instance names, if applicable. This will
help retrieve the proper mount point data later on. */
SET @instance_part = SUBSTRING(@instance_name, CHARINDEX('\', @instance_name,1)+1, LEN(@instance_name))
IF CHARINDEX('\', @instance_name,1) > 0
SET @server_name = SUBSTRING(@instance_name, 1, CHARINDEX('\', @instance_name,1)-1)
ELSE
SET @server_name = @instance_name
/* Get the most recent run date of the inventory program. This will be the basis for current size info */
SELECT @max_date = MAX(run_date) FROM Database_Growth WHERE instance_name = @instance_name
CREATE TABLE #growth (
instance_name VARCHAR(128),
instance_part VARCHAR(128),
server_name VARCHAR(128),
database_name VARCHAR(256),
PrevSizeMB DECIMAL(16,5),
CurrSizeMB DECIMAL(16,5),
TotGrowPct DECIMAL(12,2),
AvgGrowPct DECIMAL(12,2),
MonthsInPer SMALLINT,
);
DECLARE get_dbs CURSOR FOR
SELECT DISTINCT database_name from Database_Growth
WHERE instance_name = @instance_name
and database_name not in ('master', 'model', 'msdb', 'tempdb', 'distribution', 'QuestWorkDatabase')
OPEN get_dbs
FETCH get_dbs INTO @database_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @currsize = NULL
SET @prevsize = NULL
/* Because databases may have been created < 12 months ago, find the min date for this
specific database */
SELECT @min_date = MAX(run_date) FROM Database_Growth WHERE instance_name = @instance_name
AND run_date <= DATEADD(mm, -12, @max_date) AND database_name = @database_name
IF @min_date IS NULL
SELECT @min_date = MIN(run_date) FROM Database_Growth WHERE instance_name = @instance_name
AND database_name = @database_name
/* Find the most recent size for the database */
SELECT @currsize = datafile_mb
FROM Database_Growth
WHERE instance_name = @instance_name
AND database_name = @database_name
AND CAST(run_date as DATE) = @max_date
/* Find the size 12 months ago (or the minimum date, if less than 12 months) */
SELECT @prevsize = datafile_mb
FROM Database_Growth
WHERE instance_name = @instance_name
AND database_name = @database_name
AND CAST(run_date as DATE) = @min_date
/* Get the percentage growth over past 12 months */
SET @prevyrgrow = ((@currsize-@prevsize)/@prevsize*100.00)
IF @prevyrgrow < 0
SET @prevyrgrow = 0
/* If the database was just created, set the monthly growth to 0. This prevents
divide by zero errors where min_date = max_date */
IF DATEDIFF(mm, @min_date, @max_date) > 0
SET @avgmongrow = @prevyrgrow/datediff(mm, @min_date, @max_date)
ELSE
SET @avgmongrow = 0
/* Insert into temp table */
INSERT INTO #growth SELECT @instance_name, @instance_part, @server_name, @database_name, @prevsize, @currsize, @prevyrgrow, @avgmongrow, datediff(mm, @min_date, @max_date)
FETCH get_dbs INTO @database_name
END
CLOSE get_dbs
DEALLOCATE get_dbs
SELECT @totalpctgrow = (SUM(g.CurrSizeMB)-SUM(g.PrevSizeMB))/(SUM(g.PrevSizeMB)) FROM #growth g WHERE CurrSizeMB IS NOT NULL
IF @totalpctgrow < 0
SET @totalpctgrow = 0
/* Output section 1: Display overall server current size and projected growth */
SELECT g.instance_name AS ServerName,
CAST(SUM(g.CurrSizeMB) as INTEGER) as CurrTotalSizeMB,
CAST(@totalpctgrow*100.00 as DECIMAL(5,2)) as PreviousYearPctGrowth,
(CAST(SUM(g.CurrSizeMB) * @totalpctgrow as INTEGER))/2 as ProjectedGrowthMB_6mo,
CAST(SUM(g.CurrSizeMB)+((CAST(SUM(g.CurrSizeMB) * @totalpctgrow as int))/2) as INTEGER) as TotalProjectedSizeMB_6mo,
CAST(SUM(g.CurrSizeMB) * @totalpctgrow as INTEGER) as ProjectedGrowthMB_1yr,
CAST(SUM(g.CurrSizeMB)*(1.00+@totalpctgrow) as INTEGER) as TotalProjectedSizeMB_1yr,
(SELECT SUM(TotalSpaceMB) FROM Mount_Points WHERE mount_point LIKE '%'+@instance_part+'%data%' AND server_name = @server_name) as TotalDataSpaceAvailable
FROM #growth g
GROUP BY g.instance_name
/* Output Section 2: Display mount point information for server */
SELECT Server_name, Mount_Point, UsedSpaceMB, FreeSpaceMB, TotalSpaceMB, PercFree as PercentFree
FROM Mount_Points
WHERE mount_point like '%'+@instance_part+'%'
AND server_name = @server_name
/* Output Section 3: Display detailed size and growth information for individual databases */
SELECT instance_name, database_name, CAST(CurrSizeMB as INTEGER) as CurrentSizeMB, AvgGrowPct as AvgMonGrowthPct,
CAST(CASE WHEN TotGrowPct>100 THEN CurrSizeMB*2
ELSE (CurrSizeMB*POWER(1+(AvgGrowPct/100),12)) END as INTEGER) as ProjectedSize1YrMB
FROM #growth
WHERE CurrSizeMB IS NOT NULL
ORDER BY instance_name, database_name
/* Clean up and we're done */
DROP TABLE #growth
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber
,ERROR_MESSAGE() as ErrorMessage;
END CATCH
END /* End usp_ProjectedDBGrowth */
GO
And we execute it with one parameter, the instance name we’re interested in.
USE SS_Inventory GO EXEC usp_ProjectedDBGrowth 'MyInstance'
The procedure has 3 sections of output. The first part, shown below, is a summary of the current total disk usage on the instance, the previous year’s growth, the projected size in 6 months and 1 year, and the current total size of the data mount point(s). (In my SSRS report I had the projected size display in red if it was greater than the available space.)
The second section displays the mount point information, including allocated space, used space, free space, and percentage free.
The final section is where I break out the databases and you see the current size, the average monthly growth, and the projected size 1 year from now.
So, even if you choose not to put this in a pretty SSRS report, you can still execute this procedure in Management Studio and be able to tell a manager instantly whether they need to think about budgeting for more disk next year and how much they’re going to need.