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….

capture-databases-usage-stats-using-sp_spaceused-powershell/

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…