Do you have enough disk space?


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.

Also recommended:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>