Monitor SQL Server Table Size with PowerShell

By: Alejandro Cobar | Comments (1) | Related: > Monitoring

Problem

If you are a DBA providing support to a myriad of SQL Server instances in your organization it is very likely that you have hundreds (maybe thousands) of databases scattered through all of them. It is also likely that within those databases you have another gazillion tables where the data rests. There's a possibility that you will face a scenario where a particular database is eating the disk drive where its data file is hosted (either gradually or abruptly). But what if this is the case for multiple databases, in different servers, at relatively the same time, and you simply don't have a way to quickly monitor this? This tip will cover how to collect very basic information from all the tables inside databases under your support and make that information readily available.

Solution

This is a continuation of our PowerShell monitoring series and this module will gather information about tables from all specified instances. It will connect with each server in inventory.MasterServerList and capture the data for each of these instances. The connection to each SQL Server instance is based on the settings in the inventory.MasterServerList table.

Keep in mind that the goal of the series I've been working on is to give you a way to monitor your environment, even if you don't have a 3^rd party monitoring solution deployed or the one you have simply doesn't allow you to get the overall picture in a convenient format.

Prerequisites

In order to use this module, you need to create the core objects found here.

Database Objects

The only table created by the script will store the information of the tables found in the instance which is described as follows:

Table

inventory.Tables
- serverId: serverId ties back to inventory.MasterServerList.
- database_name: the name of the database where the table is hosted.
- table_name: the name of the table.
- schema: the name of the schema for the table.
- row_count: the number of rows currently in the table (at the time that the script was executed).
- total_space_mb: the number of MegaBytes that the table occupies.
- used_space_mb: the actual space (in MegaBytes) that the table is really using.
- unused_space_mb: the space (in MegaBytes) that the table is not using.
- created_date: the date/time when the table was created in the database.
- data_colletion_timestamp: datetime when the PowerShell script was executed to populate the table.

Important Note:

There will be times when you simply don't want to collect the information from all the registered instances you have in your Master Server List. To address this, simply modify the query inside the variable $instanceLookupQuery within the PowerShell script and you should be good to go.

PowerShell Script

The PowerShell script that creates the above object and inserts data into the tables inventory.Tables is called:

Get-MSSQL-Instance-Tables.ps1

If you have followed the other modules, you will notice that we have been storing all objects in "C:\temp". However, you can use any folder you want. If you make a change to the central folder location, you will need to edit the first line in the following PowerShell script to specify the new folder location.

Note: The PowerShell script will store only the information from the last execution, no historical data is retained. If you'd like to keep information from previous executions, you have to modify the script and adapt it to your particular use case.

How to Use

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

Right click on Get-MSSQL-Instance-Tables.ps1 and select Run with PowerShell

Option 2

Open a command window and navigate to the folder where you saved the above files and run

powershell "C:\temp\Get-MSSQL-Instance-Tables.ps1"

Option 3

Schedule this as a SQL Server Agent Job to run on a regular basis.

Option 4

Schedule this as a Windows Task Scheduler job to run on a regular basis.

Check Creation of Database and Objects

After running the PowerShell script, we can see the objects that are created.

If we query the inventory.Tables, we can see the data that has been collected.

To demonstrate a useful scenario, in my 2 instances that I use as punching bags, I have created a database called test and a table called t1 in each. The idea is to exemplify a case where you provide support for a specific 3^rd party software for multiple tenants which will pretty much have the same structure across the board.

In this case, I have inserted 5 million records in the table inside instance 1 and 1 million records in instance 2. This would mean that the tenant using instance 1 is having more activity than the other one.

Although this is a very basic example, it has been very useful to me because I've been able to detect things like: the volume of this particular table is very different for this particular tenant than the rest; and even though in some cases it is expected, sometimes I've found that it is because there are certain database objects (SPs, functions) that are outdated which cause this behavior.

Checking for Errors

To check for errors, query the monitoring.ErrorLog table using the following query:

SELECT *
FROM monitoring.ErrorLog
WHERE script = 'Get-MSSQL-Instance-Tables'

If you'd like to know the SQL Server instance that got the errors, you would have to issue the query like this:

SELECT 
   CASE 
      WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance,
   e.script,
   e.message,
   e.error_timestamp
FROM monitoring.ErrorLog e
JOIN inventory.MasterServerList msl ON msl.serverId = e.serverId
WHERE e.script = 'Get-MSSQL-Instance-Tables

Useful Queries

Show me the list of tables sorted by the number of rows from high to low.

SELECT 
   CASE 
      WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance, 
   t.database_name,
   t.table_name,
   t.schema,
   t.row_count,
   t.total_space_mb,
   t.used_space_mb,
   t.unused_space_mb,
   t.created_date 
FROM inventory.Tables t
JOIN inventory.MasterServerList msl ON msl.serverId = t.serverId
ORDER BY t.row_count DESC

Show me the list of tables sorted by the amount of total space they occupy.

SELECT 
   CASE 
      WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name 
      ELSE CONCAT(msl.server_name,'\',msl.instance) 
      END AS instance, 
   t.database_name,
   t.table_name,
   t.schema,
   t.row_count,
   t.total_space_mb,
   t.used_space_mb,
   t.unused_space_mb,
   t.created_date 
FROM inventory.Tables t
JOIN inventory.MasterServerList msl ON msl.serverId = t.serverId
ORDER BY t.used_space_mb DESC

NOTE - Sometimes it is not enough just to look at the row count of a table to determine if a table is big or not. You also have to consider the actual space that it occupies because there might be cases where a table is relatively small in row count, but if it stores wide fields (i.e. BLOB, VARCHAR(MAX)), it can be a big one.

Download Scripts

Download the scripts for this module

Next Steps

An idea would be to keep track of the growth of each table during a whole month and check for trends.
You could mix the result set of this module with the ones that collect information about the databases and the indexes for all the instances.
Check out the other parts of this series

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips