Monitor SQL Server Table Size with PowerShell


By:   |   Updated: 2020-12-15   |   Comments   |   Related: More > 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 3rd 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.

database objects

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 3rd 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.

query results

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

Next Steps


Last Updated: 2020-12-15


get scripts

next tip button



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

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Determining space used for all tables in a SQL Server database

How to Read Log File in SQL Server using TSQL

Methods to determine the status of a SQL Server database

How to setup SQL Server alerts and email operator notifications

Different techniques to identify blocking in SQL Server














get free sql tips
agree to terms