How to Capture Database(s) usage stats using sp_spaceused and PowerShell

The objective of this article is to look at the possible ways of capturing database usage trends using the most commonly available tools and techniques. The integration of SQL, the use of sp_spaceused, and the flexibility offered by PowerShell have been used to get the required database growth patterns; this data is combined into a single result. After the transformation is done, the data is stored in a dedicated repository.

Continue reading….


Wrap Up

In this article, we looked at modifying the output of sp_spaceused system stored procedure, and fetching the raw data that can be put to use after some transformations. We primarily looked at overcoming the challenge of combining the two result sets, using PowerShell to combine the data into a friendly JSON report, which can then be transformed into a SQL table, stored in a central repository; we can now use this repository as a source of raw data which can be used to generate meaningful reports. Also, we saw the integration with OPENROWSET (BULK) function to import JSON files into SQL Server using JSON constructs.

Fetching the raw data and storing is important. When processed, this data would be helpful in capacity planning and forecasting. We shall discuss on how to put this data to use in capacity planning, in a separate article.

Happy Learning…



About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: jayaram/ Connect Me: Twitter @prashantjayaram GMAIL The articles are published in:
This entry was posted in JSON, SQL, SQL 2012, SQL 2016, T-SQL and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s