SQL Server Database and Database File Monitoring with PowerShell

By: Alejandro Cobar | Updated: 2020-07-30 | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | > Monitoring

Problem

In this PowerShell script, we will collect information about databases and database attributes for monitored SQL Server instances.

Solution

This module will gather information about the current inventory of databases in each SQL Server instance. It will connect with each server in inventory.MasterServerList and capture the data related to databases for each of these instances.

Prerequisites

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

Database Objects

For this module, two tables will be created (in case they don’t already exist). The first table stores basic information about each database and the second table stores information about the data files for each database.

This is the structure of the first table, so you can get an idea of what data will be stored.

inventory.Databases
- databaseId - unique value for each database
- serverId - serverid ties back to inventory.MasterServerList
- database_name - name of database
- total_size - size of database
- state - current status: online, offline, etc.
- owner - owner of database
- compatibility_level - database compatibility level
- created_date - date database was created
- is_broker_enabled - if service broker is enabled
- is_master_key_encrypted_by_server - if there is a master key
- is_auto_shrink_on - if auto shrink is on
- is_auto_close_on - if auto close is on
- is_encrypted - if the database is encrypted
- is_auto_update_stats_on - if auto update statistics is on
- data_collection_timestamp - when data was last collected

I selected a set of fields relevant to the databases metadata, but if you want to dig a bit deeper on the array of available options, you can take a look at Microsoft’s documentation here and pick whatever you want.

Note, if you want to add more fields to either of the tables, make sure to adjust the structure within the PowerShell script and adapt the respective logic that will handle that additional field.

This is the structure of the second table. Within this table you will see relevant information specific only to the databases data files (data and log).

*All sizes are in Megabytes.

inventory.DBDataFiles
- databaseId - ties back to inventory.Databases
- file_type - type of data file
- logical_name - logical name of data file
- file_path - location of file
- size - size of the file
- max_size - maximum size of the file
- growth - file growth settings
- is_read_only - if file is read only
- is_percent_growth - percent value if growth is based on percent
- data_collection_timestamp - when data was last collected

PowerShell Script

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

Get-MSSQL-Instance-Databases.ps1

The script has some validation logic that will help you check if some key elements exists for the script to run successfully. For instance, it will confirm that the inventory.MasterServerList table exists and that it has at least 1 active instance is registered.

If you have followed along with the other modules, you have noticed that we have been storing all objects in "C:\temp", but 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 PowerShell script to specify the new folder location.

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-Databases.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 this command:

powershell "C:\temp\Get-MSSQL-Instance-Databases.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 tables that were created.

This is an example of the data that the PowerShell script collected for 2 instances I’m using for demonstration purposes.

The tables are a bit wide, so for the sake of legibility, I trimmed them to show enough information that can give you a good idea of what the result will look like.

Important note: the PowerShell script is crafted in a way that it will not store information from previous executions. This is something that would have to be modified by the end user if such capability is desired, the script only stores information from the very last execution.

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-Databases'
Query Collected Data

If you’d like to know the SQL Server instance that had errors, you would have to issue a 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
WHWHERE e.script = 'Get-MSSQL-Instance-Databases'

Useful Cases

If you regularly collect this information from the infrastructure under your support, you can have visibility over things like:

What are the largest databases I’m currently supporting in my environment?

SELECT * FROM inventory.Databases
ORDER BY total_size DESC

To adhere to best practices, I would like to know which database files are hosted on the C:\ drive?

SELECT    CASE WHEN msl.instance = 'MSSQLSERVER' THEN msl.server_name ELSE CONCAT(msl.server_name,'\',msl.instance) END AS instance,
   db.database_name,
   df.file_path
FROM inventory.DBDataFiles df
JOIN inventory.Databases db ON df.databaseId = db.databaseId
JOIN inventory.MasterServerList msl ON db.serverId = msl.serverId
WHWHERE SUBSTRING(file_path,0,2) = 'C'

I would like to know which databases have an owner different than sa?

SELECT *FROM inventory.Databases
WHERE owner <> 'sa'

I would like to know which databases are using Service Broker?

This one seems to be extremely trivial, but here’s a tip you might not be aware about:

If a database is using Service Broker, such setting is not preserved after the database is restored from a backup. Therefore, it might be a good idea to take note of which databases are using Service Broker because if you ever have the need to restore one of them, you will have to manually turn on this option after the backup has been restored.

SELECT *
FROM inventory.Databases
WHERE is_broker_enabled = 1

I would like to know which databases are not online?

This might be helpful to you to detect anything weird going in your environment.

SELECT *FROM inventory.Databases
WHERE STATE <> 'ONLINE'

I would like to know which databases are configured to auto-shrink?

As a proactive DBA, you might be interested to know If you have databases with this enabled. As a general rule of thumb, it is a bad idea to have a database using auto-shrink.

SELECT *ROM inventory.Databases
WHERE is_auto_shrink_on = 1

Download Scripts

Download the scripts for this module

Next Steps

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

Article Last Updated: 2020-07-30