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 | More > Monitoring
In this PowerShell script, we will collect information about databases and database attributes for monitored SQL Server instances.
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.
In order to use this module, you need to create the core objects found here.
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.
- 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.
- 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
The PowerShell script that creates the above object and inserts data into the inventory.Databases and inventory.DBDataFiles tables is called:
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:
- Right click on Get-MSSQL-Instance-Databases.ps1 and select Run with PowerShell
- Open a command window and navigate to the folder where you saved the above files and run this command:
- 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 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'
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
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 SQL Agent Jobs
About the author
View all my tips
Article Last Updated: 2020-07-30