Monitor SQL Server Table Size with PowerShell
By: Alejandro Cobar | Updated: 2020-12-15 | Comments (1) | Related: More > Monitoring
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.
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.
In order to use this module, you need to create the core objects found here.
The only table created by the script will store the information of the tables found in the instance which is described as follows:
- 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.
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.
The PowerShell script that creates the above object and inserts data into the tables inventory.Tables is called:
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:
- Right click on Get-MSSQL-Instance-Tables.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run
- Schedule this as a SQL Server Agent Job to run on a regular basis.
- 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 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.
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
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.
- 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
- Monitoring SQL Server with PowerShell Core Object Setup
- Monitoring SQL Server with PowerShell Instance Data Collection
- Monitoring SQL Server with PowerShell Instance Jobs Collection
- Monitoring SQL Server with PowerShell Instance Jobs Last Execution
- Monitoring SQL Server with PowerShell Instance Backups
- Monitoring SQL Server with PowerShell Instance Database and Database Files
About the author
View all my tips
Article Last Updated: 2020-12-15