Monitoring Table Growth with PowerShell

I have quite a few SQL servers, and monitoring the growth of tables on them is a real hassle. I finally got tired of it and decided to build my own solution which, given my scenario, had to have these features:

  1. All the collected data had to be stored in a separate server from the monitored systems.
  2. I can’t use linked servers.
  3. I didn’t want to use TSQL scripts in every server to collect the data. If I change the script on one server, I’d then have to change it on all the servers. That’s a major headache, so I wanted to centralize the script.
  4. I didn’t want to use a third-party tool.

I’ve been hearing about windows PowerShell as a method for administering Windows servers (especially Exchange Server 2007) for quite a while, and since last year I’ve become an avid enthusiast. By itself, PowerShell has already helped me in several situations, but when you combine it with SMO, as my fellow enthusiasts will know, it becomes a very formidable tool. You’ll see this very clearly in my solution, where I used:

  • One central Server to collect the data, with a table called tablegrowth on a database called DBA.
  • A PowerShell script to collect the data about the various tables.
  • One trigger in my data repository to calculate the tables’ growth between collections.

I’m NOT going to cover what PowerShell is, how to write PowerShell scripts or what SMO (SQL Server Management Objects) are. There is already plenty of information about those out on the web, and I’m only here to talk about one thing today: monitoring table growth.

So let’s go to the solution.

Setting up a Collect Data Repository

In my first collections I didn’t have a SQL server repository, and I was using my desktop computer to hold all my data. These days, I have a server to centralize all my scripts and data (much nicer!), and in the script I’ll show you today this server is called “CollectDataServer“. To start, let’s create a database called DBA – to be honest, the configuration of this server, database…etc is entirely up to you. You just need a SQL server and a database.

With this done, let’s create the table that we’ll store the collected data in:

And finally, we’ll need to create the trigger. This trigger is responsible for collecting the information about the size of a table’s data and index, and then subtracting the size of the last collection to find the table growth:

With our environment now configured, we can use the PowerShell Script to collect the data.

PowerShell Script

First we have to create a flat file (.txt) with all the servers to be monitored. In my case I have 7 servers, so the list (stored in c:serversservers.txt folder) will look something like this:

Server1
Server2
Server3
Server4
Server5
Server6
Server7

Now let’s see the PowerShell script:

This collects the data from all the servers in my txt file. The system databases and system tables are all ignored, and it can be modified to collect data from only some tables, or some databases… how it’s fine-tuned is entirely up to you.

This script uses SQLPS.EXE (SQL Server 2008) because I’m using the Invoke-Sqlcmd cmdlet. If you want to use the PowerShell shell, I just add the SQL server snapins :

Now it’s just a case of scheduling, and we’ll have everything in position. I use SQL server 2008 and PowerShell jobs to do this, but you can use powershell.exe to schedule in windows, too.

Retrieving the data

Lastly, to retrieving my data for table growth in January, organized by server (for example), here’s what I need:

… And the data is collected. Now you just need to retrieve it any way you like it.