Prashanth Jayaram

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

September 11, 2017 by

Today’s businesses work with huge volumes of data. Sometimes, understanding the granularity of the data is helpful in data administration. Understanding the granularity poses unique challenges though and the database administrator needs to balance the key business and technical metrics of the environment. One of the key technical metrics is disk space estimation, which is vital to capacity planning and forecasting. The simplest way to get this information is by using the system stored procedure, called, sp_spaceused. The growth metrics can be captured periodically and stored in a central repository, a repository dedicated to helping with capacity planning and forecasting the disk requirements. This is a critical part of the day-to-day activities of a database administrator.

Introduction

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.

Background

The output of the stored procedure, sp_spaceused, is a pair of result sets when the @objname parameter is omitted. The stored procedure accepts the @oneresultset parameter starting from SQL 2016, therefore, the result can be viewed in a single result pane. The simulation can be derived using DMVs but the result may vary in terms of numbers, sometimes, based on the use of complex design and data structures. This introduces a level of complexity in the process. To overcomes, this, we use PowerShell with SQL, in order to integrate the two result-sets into one and seamlessly executing the scripts across several servers. The generated stream of data is converted into JSON, the lightweight data interchange format. Using JSON helps simplify the integration of the output into a dedicated repository for data transformation.

Through this article, let’s learn how to integrate SQL and PowerShell to execute stored procedures, as well as the techniques to transform the output into the desired form which focuses on the database usage trends.

We shall discover:

  • the workings of sp_spaceused,
  • how the stored procedure is executed to transform and merge the result,
  • loading of the JSON data,
  • the available JSON constructs,
  • and more…

Getting Started

The sp_spaceused stored procedure

sp_spaceused is the most common stored procedure used by any database administrator to check the database space usage details. It displays the number of rows, the disk space reserved, and the disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

If objname is omitted, the following result sets are returned with the current database size information.

Name Data type Description
database_name nvarchar(128) Name of the database.
database_size varchar(18) Size of the database in MB. It includes both data and log files.
unallocated space varchar(18) Space available but yet to be reserved for database objects

Name Data type Description
reserved varchar(18) Total space allocated by database objects
data varchar(18) Total data space
index_size varchar(18) Total indexes space
unused varchar(18) Total space reserved but not yet used.

Permissions

Permission to execute sp_spaceused is granted to the Public role. Only members of the db_owner fixed database role can specify the @updateusage parameter.

In SQL Server 2016, the @openresultset parameter made our life much simpler. By specifying the parameter, the result can be shown in one or more result sets.

For example, using sp_spaceused @oneresultset=1, summarizes the space usage details for the context of current database into a single result set.


In the following example using sp_spaceused @oneresultset=0, shows the space usage details for the context of current database in two result sets; this is the default – if we omit the parameter, the result will be displayed in multiple result sets.


Create the PowerShell script

Let us walk through the script, step-by-step, along with looking at the details of the setup and configuration of the script. The complete script can be found in Appendix (A)

Step 1: Declare the variable

Step 2: Declare connection object

  • $DataSet1: To store sp_spaceused output of the first result pane
  • $DataSet2: To store sp_spaceused output of the second result pane

Step 3: Build connection string to prepare database lists

Step 4: Loop through the listed databases

Step 5: Merge the datasets

Step 6: Conversion to JSON

In this step, we shall generate the data, and pipe that to ConvertTo-Json to get the output as a JSON file.

Step 7: Call the Get-SpaceUsed PowerShell function

Create a text file with the names of the database servers you’d like to get the information for. Let’s call this server.txt. We’ll call this file when calling the function.

Call the PowerShell function

The output of the function is the two result sets merged into one.

PS P:\> Get-SpaceUsed -Inputfilename \\hqdef\c$\server.txt -JSONoutputFile C:\spaceused.JSON

Step 8: Transformation of the JSON into an SQL table

The aforementioned steps yield data in the JSON format, which can be used to generate a JSON file, As for the PowerShell script, it can be called using an SQL Server agent job or Windows Task Scheduler; the script would generate the JSON file. The subsequent step would be to load the data into SQL Table using JSON constructs. This is one of the most recommended ways to load data in JSON format, into an SQL table.

Let us now view the output JSON File

Let us now import the JSON data file into the SQL Server using JSON constructs. The output is joined with OpenRowSet bulk loading function that can read the data from the file. The function returns a single column that contains the contents of the file. The output is then joined using the OPENJSON function. The CROSS APPLY enables us to join the dynamically generated rows from the OPENROWSET function, which is a collection of JSON objects stored in a single column. You can further parse the ServerName as shown in the below query, to fetch the desired result using native JSON SQL constructs.


After collecting the database usage stats over a period of time, we can project the growth rate by self-joining the tables with the previous day’s values. The trend can be plotted on all the columns of the sp_spaceused output columns, and the data can also be aggregated at the instance level.

Step 9: Cleansing SQL data

Now that we’ve polled our servers and written information to our repository, let’s now query it to help in capacity planning. In the following output, we can see the how much free space left in the database. The logdate column is used for log the specific calendar date that an entry was made into the dedicated table.


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.

References

Appendix (A)


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
SQL Database development

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

397 Views
  • Prashanth Jayaram

    Hello Jeff,
    Thanks for reading the article. Yes, you are right. In SQL Server 2016, by specifying the @openresultset parameter, the result can be shown in one or more result-sets. Please refer the Getting Started section of the article. I have mentioned this with an example. This is multi-server script. It can be run on all the versions of SQL Server. Thanks again for the comment.
    Regards,
    Prashanth