By: Tim Smith | Comments (4) | Related: More > Database Administration
Problem
One aspect of SQL Server database administration is monitoring and handling database growth. In this tip we look at how you can monitor SQL Server database growth against expected growth using PowerShell.
Solution
In this tip, I'll provide some tools for the above issue and use the following scripts in three situations:
- To get a feel for the growth of an environment when introduced to it (I don't use the expected growth parameter here). This is especially useful when speaking with developers about their environment, as one common question I'll ask is about the current size and growth and this performs a check over a time period.
- To monitor a current environment's growth and measure how accurate my expectations are with the actual growth. In its own way, this also tells me how familiar I am with the environment.
- To begin planning ahead for an altered database model, if the growth indicates that this will be necessary in the long run (i.e.: like archiving data, in some situations).
We will structure our script in a manner that allows us to call it on whatever time scale we choose. Also, for those of us who've worked in many different environments - OLTP, OLAP, mixed - we may or may not choose to monitor the size of the log. Whether you should or should not allow auto growth in your environment lies outside the scope of this tip - we'll be looking at the database size without the free space.
In the first script, we can either input our growth expectation or not. If we don't, we can use the second T-SQL table structure that doesn't store the expected growth. If we never expect the growth of our environment to change (example: we only expect that it will change 5% a month), then we don't need to store the expected growth as we can do the multiplication with T-SQL; if it can change, based on the past, we can store it to evaluate how we're pacing with our benchmark.
Function Size_DBs ($server, $db, $smo, $ep = $null)
{
$nl = [Environment]::NewLine
Add-Type -Path $smo
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$dse_arr = @()
foreach ($d in $srv.Databases | Where-Object {($_.IsSystemObject -eq $false)})
{
$d_ID = $d.ID
## Per Microsoft, the SpaceAvailable property in the database class is a float-64 measured in KB, so we convert it to MB
$d_US = ($d.Size - ($d.SpaceAvailable/1024))
if ($ep -ne $null)
{
$d_EX = (($d.Size - ($d.SpaceAvailable/1024))*$ep)
$dse_arr += $nl + "INSERT INTO Logging_Sizes (DatabaseID, DatabaseSize, ExpectedGrowth) VALUES (" + $d_ID + "," + $d_US + ", " + $d_EX + ")"
}
else
{
$dse_arr += $nl + "INSERT INTO Logging_Sizes (DatabaseID, DatabaseSize) VALUES (" + $d_ID + "," + $d_US + ")"
}
}
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER="+ $server +";DATABASE=" + $db + ";Integrated Security=true"
$ld = New-Object System.Data.SqlClient.SqlCommand
$ld.CommandText = $dse_arr
$ld.Connection = $scon
$scon.Open()
$ld.ExecuteNonQuery()
$scon.Close()
$scon.Dispose()
}
Size_DBs -server "OURSERVER\OURINSTANCE" -db "Logging" -smo "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -ep 1.05
<#
---- Store the expected growth:
CREATE TABLE Logging_Sizes(
DatabaseID INT,
DatabaseSize DECIMAL(22,2),
ExpectedGrowth DECIMAL(22,2),
MeasureDate DATETIME DEFAULT GETDATE()
)
---- Don't store expected growth:
CREATE TABLE Logging_Sizes(
DatabaseID INT,
DatabaseSize DECIMAL(22,2),
MeasureDate DATETIME
)
#>When we call the PowerShell script, we're passing in the server, the logging database, the SMO dll location, and our expected growth - $ep (or we can choose to not pass this value to the script and use the second table example from the above code). For instance, in the opening list, I wouldn't pass in an expected value because I am new to the environment and am only tracking to get a feel for it. To see how we could review these data (once they're added to the table), let's use a test example to see how we can look at these data. In this test case, we'll monitor the database size for three months of two databases, manually inserting in data using T-SQL. From there, we rank the dates, then evaluate the growth in percent of the database size as well as the difference in MB of our expectations. If DBAs wanted to look at the actual values, they could also uncommented the sizes for each month.
---- Test Data INSERT INTO Logging_Sizes VALUES (5,201.06,211.68,'2014-07-01') , (6,203.47,213.64,'2014-07-01') , (5,202.01,212.11,'2014-08-01') , (6,225.63,236.91,'2014-08-01') , (5,201,211.05,'2014-09-01') , (6,278.23,292.14,'2014-09-01') ;WITH ActualVExpected AS( SELECT DENSE_RANK() OVER (ORDER BY MeasureDate) DtRk , * FROM Logging_Sizes ) SELECT t.DatabaseID --, t.DatabaseSize LastMonthSize --, tt.DatabaseSize ThisMonthSize , (((tt.DatabaseSize-t.DatabaseSize)/t.DatabaseSize)*100) PercentGrowth , tt.DatabaseSize - t.ExpectedGrowth ExpectationDifference FROM ActualVExpected t INNER JOIN ActualVExpected tt ON t.DatabaseID = tt.DatabaseID AND t.DtRk = (tt.DtRk - 1)
In some environments, if we come across a database of a certain size, or if a database were to grow a certain size that could be an issue. Imagine an OLAP database built to handle a terabyte of data that crosses the 500GB size - for some environments, this might be time to pass in an alert. We can add the below if statement in those situations to send an email (or, you can also use your carrier to send a text), and also add the parameter $size to our function. Because some environments will be satisfied with tracking logging, this may not be a step that some will need to add ("it depends"). From a standpoint of being proactive, I would delineate a point at which you want to be alerted (in environments where growth can't happen, I want to know when developers are trying to push data into the database - though, in these cases I'll be looking at the logs).
# 1024 MB equal 1GB; so if we wanted to alert on 500 GB, 1024*500; we would pass in that value
if ($d_US -gt $size)
{
$v = $size/1024
$db_b = $d.Name + " now exceeds $v GB"
$db_s = $d.Name + " Exceeds $v GB"
Send-MailMessage -From "[email protected]" -To "[email protected]" -SmtpServer "smtp.OurEnvironment.com" -Subject $db_s -Body $db_b -Priority High
}We could also change the measurements here for our environment, as this depends. Some environments think 500 gigabytes of data are small, while others may only be managing 10 or so gigabytes. In these situations, measuring the database used space from above in gigabytes (i.e.: $d.SpaceAvailable/(1024*1024))) might be more appropriate, and $v would now be measuring in terabytes.
Finally, some environments may want to look at the data growth and ignore the log growth (or, in some cases, this may not be applicable to the environment). In the altered script below, I loop through each log file, adding the log size (per Microsoft, the size property of the LogFile class is measured by a double in kilobytes, so we would divide by 1024 to get megabytes) to the variable $l and subtracting that total size from the used space we calculated already calculated when subtracting the space available from the database size.
Function Size_DBs ($server, $db, $smo, $ep = $null)
{
$nl = [Environment]::NewLine
Add-Type -Path $smo
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$dse_arr = @()
foreach ($d in $srv.Databases | Where-Object {($_.IsSystemObject -eq $false)})
{
$d_ID = $d.ID
## Handling the log
$l = 0
foreach ($lg in $d.LogFiles)
{
$l += $lg.Size/1024
}
$d_US = (($d.Size - ($d.SpaceAvailable/1024)) - $l)
if ($ep -ne $null)
{
$d_EX = (($d.Size - ($d.SpaceAvailable/1024))*$ep)
$dse_arr += $nl + "INSERT INTO Logging_Sizes (DatabaseID, DatabaseSize, ExpectedGrowth) VALUES (" + $d_ID + "," + $d_US + ", " + $d_EX + ")"
}
else
{
$dse_arr += $nl + "INSERT INTO Logging_Sizes (DatabaseID, DatabaseSize) VALUES (" + $d_ID + "," + $d_US + ")"
}
}
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER="+ $server +";DATABASE=" + $db + ";Integrated Security=true"
$ld = New-Object System.Data.SqlClient.SqlCommand
$ld.CommandText = $dse_arr
$ld.Connection = $scon
$scon.Open()
$ld.ExecuteNonQuery()
$scon.Close()
$scon.Dispose()
}
Size_DBs -server "OURSERVER\OURINSTANCE" -db "Logging" -smo "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -ep 1.05Next Steps
- Test and adjust the script in a test environment.
- What is the expected growth of your environment for each new measurement?
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
