Monitor SQL Server Database Size, Growth and Expected Growth with PowerShell


By:   |   Updated: 2014-11-12   |   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:

  1. 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.
  2. 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.
  3. 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.05
Next Steps
  • Test and adjust the script in a test environment.
  • What is the expected growth of your environment for each new measurement?


Last Updated: 2014-11-12


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources





Comments For This Article




Wednesday, December 21, 2016 - 11:55:17 PM - Amar Kumbhar Back To Top (45027)

 

Thanks a lot, this solved my problem. Thank you very much useful !


Monday, November 17, 2014 - 7:11:58 AM - Tim Back To Top (35308)

Hey DC, saw your question today.  If you go to MSDN (msd.microsoft.com), and use their search bar at the top right, you can search for documentation.  In the case you listed, I frequently use both the SqlConnection and SqlCommand class because I find the "built in" approach (ie: Invoke-Sqlcmd) very restrictive.  Documentation on classes comes with information on their constructors, methods, properties, events, etc often with C# examples.


Monday, November 17, 2014 - 3:31:45 AM - Ravi Back To Top (35307)

Thnak You for your valubale feedback.


Wednesday, November 12, 2014 - 2:19:25 PM - D.C. Back To Top (35270)

Tim,

 

Do you have, or where's the best place, to find documentation on the properties, values and uses of the two SQL objects (from your example above) -

  • $scon = New-Object System.Data.SqlClient.SqlConnection

and

  •  $ld = New-Object System.Data.SqlClient.SqlCommand

Thanks!



download





Recommended Reading

How to rename a SQL Server database

How to determine SQL Server database transaction log usage

Different ways to determine free space for SQL Server databases and database files

How to read the SQL Server Database Transaction Log

Execute SQL Server Script Files with the sqlcmd Utility








get free sql tips
agree to terms


Learn more about SQL Server tools