Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2014-11-12   |   Comments (4)   |   Related Tips: 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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 

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


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

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

Thnak You for your valubale feedback.


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

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!


Learn more about SQL Server tools