Introduction

This article shows one possible solution for monitoring disk space usage using SQL Server and a simple PowerShell script by parsing XML data and Using ADO.NET class libraries. The idea is borrowed from the external blog Monitoring Disk Space with SQL Server and PowerShell. Have made customization like adding server name to a central table and reading server name from the input file which acts as a source for an entire script Also, the SP is altered to add server name. 

Note: The intention behind this article is to show the different methods of capturing disk space metrics.

↑ Return to Top



Step by Step details

The section talks about the high-level objectives of this article
  • Input File "Server.csv"
  • Create table "DiskVolume"
  • Prepare XML data
  • Parsing XML data using openXML
  • Call PoSH script
  • Insert data into repository using SP

Input File

To execute the script, the input file is necessary. First, create a “Server.csv” file containing all the SQL Servers that your intend to monitor and store disk space usage metrics

Table Creation

In this first code section is the table definition I will be using to store the data collected by the PowerShell script. Please note that I have opted to define the [UsedSpaceKB], [PercentFree] and [PercentUsed] fields as computed columns to simplify the reports which will be utilizing this data. I have also chosen to use kilobytes (kb) as my unit of measurement, You can customize as per your requirement. The columns are self-explanatory. 

CREATE TABLE [dbo].[DiskVolume]
(
    [Server] varchar(25) NOT NULL,
   ,[DiskVolumeID] INT IDENTITY(1, 1) NOT NULL
    ,[CheckDate] DATETIME NOT NULL
    ,[Name] VARCHAR(128) NULL
    ,[Label] VARCHAR(128) NULL
    ,[DriveLetter] VARCHAR(2) NOT NULL
    ,[CapacityKB] BIGINT NOT NULL
    ,[FreeSpaceKB] BIGINT NOT NULL
    ,[UsedSpaceKB] AS ([CapacityKB] - [FreeSpaceKB])
    ,[PercentFree] AS (CONVERT(DECIMAL(18, 2), CAST([FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100))
    ,[PercentUsed] AS (CONVERT(DECIMAL(18, 2), CAST([CapacityKB] - [FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100))
)
ON [PRIMARY];

Prepare XML document

This section prepares the XML document to demonstrate the use of sp_xml_preparedocument and openXML clause. The below code generates the valid XML document consists of root level, top level, and child level tags.

$server='hqdbsp18'
[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName $Server Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
[string]$XmlData = "<root><cimv2>";
$Volumes | % { $XmlData = $XmlData + "<Win32_Volume ServerName=`"" + $Server + "`" Name=`"" + $_.Name + "`" Label=`"" + $_.Label + "`" DriveLetter=`"" + $_.DriveLetter + "`" Capacity=`"" + $_.Capacity + "`" FreeSpace=`"" + $_.FreeSpace + "`"></Win32_Volume>" };
$XmlData = $XmlData + "</cimv2></root>";
$XmlData

Parsing XML Document

This next code section shows the definition of the procedure used to insert data into the table above. The idea is to pass in one XML parameter as data, containing all the information collected from all the disk drives. The procedure then reads the XML text provided as input, parses the text by using the MSXML parser, and then inserts the parsed data into the table.The second parameter specifies how many days of data to retain in the table and then the procedure purges any expired data after completing the insert.
sp_xml_preparedocument hdoc OUTPUT, xmltext, xpath_namespaces

sp_xml_preparedocument is a Microsoft shipped extended stored procedure to parse xml document in SQL Server

  1. hdoc: This is an output parameter of INT datatype, this generates a handle of parsed XML.
  2. Xmltext: XML document to parse.
  3. xpath_namespaces: The namespace of XML, if you are parsing an untyped XML then this parameter is not mandatory. Default is <root xmlns:mp=”urn:schemas-microsoft-com:xml-metaprop”>

OPENXML: This is the next step after parsing and getting a handle from sp_xml_preparedocument procedure. OPENXML is a function to read the xml into a table.

SELECT * FROM OPENXML (@hdoc, rowpattern, flag) WITH (Column patterns)

@hdoc: Is an output variable of hdoc (XML handle)

Row pattern: An XML can have many tags like root tags, top-level tags and many child tags. So row pattern defines an XPATH, telling your query, from where to start looking for the data in XML while shredding.

Column pattern: we know row pattern is useful for targeting a level and start searching the data from the target level, but what if some of the data are out of the targeted row pattern level and are present in one upper or lower level? You can’t change the row pattern, so you will have to use column pattern to reach out one upper or lower level to fetch the required data.

flags :Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter. The flag value is set to 0 which indicates the sql is using default mapping

DECLARE @xml xml, @hdoc int
SET @xml = '
<root><cimv2>
<Win32_Volume ServerName="HQDBSP18" Name="C:\" Label="OS" DriveLetter="C:" Capacity="42947571712" FreeSpace="8456220672"></Win32_Volume><Win32_Volume ServerName="HQDBSP18" Name="E:\" Label="Apps" DriveLetter="E:" Capacity="8587833344" FreeSpace="4280815616"></Win32_Volume><Win32_Volume ServerName="HQDBSP18" Name="F:\" Label="Data" DriveLetter="F:" Capacity="220113924096" FreeSpace="24309551104"></Win32_Volume>
<Win32_Volume ServerName="HQDBSP18" Name="G:\" Label="logs" DriveLetter="G:" Capacity="51537506304" FreeSpace="41026211840"><Win32_Volume>
 
</cimv2></root>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml --Preparing XML handle
SELECT * FROM    OPENXML(@hdoc, '/root/cimv2/Win32_Volume', 0)  --Row Pattern
WITH (
[ServerName] VARCHAR(128) '@ServerName' --column Pattern
                ,[Name] VARCHAR(128) '@Name'
                ,[Label] VARCHAR(128) '@Label'
                ,[DriveLetter] VARCHAR(2) '@DriveLetter'
                ,[Capacity] BIGINT '@Capacity'
                ,[FreeSpace] BIGINT '@FreeSpace')
   
EXEC sp_xml_removedocument @hdoc --Releasing memory

Here in above query, row pattern is "/root/cimv2/win32_Volume”, that means, the starting point is “win32_volume” tag. For each column, we have different column patterns defined, “ServerName”, "Name","Label","Label","DriveLetter", "Capacity","FreeSpace" under “win32_Volume” element, so we did not have to traverse any level up or down to get the data. Its pretty straight forward hence the default flag setting is in place.

This is another Microsoft shipped extended stored procedure to remove the XML handle and release the memory that was generated by sp_xml_preparedocument while parsing and storing the XML document. It's important to release the memory after shredding the XML otherwise, this will keep the memory allocated until the session is closed.

EXECUTE [dbo].[sp_xml_removedocument] @hdoc;

Stored Procedure Creation

This section creates the stored procedure on the central server where you already create the repository to store the disk space usage metrics

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[InsertDiskVolume]') AND [type] IN (N'P', N'PC'))
BEGIN
    EXECUTE ('CREATE PROCEDURE [dbo].[InsertDiskVolume] AS RAISERROR(''UNDEFINED!'', 16, 1);');
END;
GO
   
  
ALTER PROCEDURE [dbo].[InsertDiskVolume]
(
    @XmlData XML,
    @HistoryDays SMALLINT = 90
)
AS
BEGIN
    SET NOCOUNT ON;
   
    DECLARE @hdoc AS INT;
   
    --Create an internal representation of the XML document.
    EXECUTE [dbo].[sp_xml_preparedocument] @hdoc OUTPUT, @XmlData;
   
    INSERT INTO [dbo].[DiskVolume]
    (
            [Server]
            ,[CheckDate]
            ,[Name]
            ,[Label]
            ,[DriveLetter]
            ,[CapacityKB]
            ,[FreeSpaceKB]
    )
    SELECT   [ServerName]=[Volume].[Servername]
            ,[CheckDate] = CURRENT_TIMESTAMP
            ,[Name] = [Volume].[Name]
            ,[Label] = [Volume].[Label]
            ,[DriveLetter] = [Volume].[DriveLetter]
            ,[CapacityKB] = [Volume].[Capacity] / 1024
            ,[FreeSpaceKB] = [Volume].[FreeSpace] / 1024
    FROM     OPENXML(@hdoc, '/root/cimv2/Win32_Volume', 0)
             WITH
             (
                [ServerName] VARCHAR(128) '@ServerName'
                ,[Name] VARCHAR(128) '@Name'
                ,[Label] VARCHAR(128) '@Label'
                ,[DriveLetter] VARCHAR(2) '@DriveLetter'
                ,[Capacity] BIGINT '@Capacity'
                ,[FreeSpace] BIGINT '@FreeSpace'
             )
             AS [Volume];
   
    EXECUTE [dbo].[sp_xml_removedocument] @hdoc;
      
    DELETE
    FROM    [dbo].[DiskVolume]
    WHERE   [CheckDate] < DATEADD(d, -@HistoryDays, CURRENT_TIMESTAMP);
END;

Execute PoSH script

Now all we need is the Powershell script to generate the same XML structured data to pass into the stored procedure. The following is the PowerShell code, created to generate the XML data and calls the insert procedure. You should be able to copy/paste the PowerShell code below into your IDE of choice and run it without error. I’ve highlighted line #7 where you will need to change the connection string parameters(Server and Database) and line #12 where you will need to change the procedure name if needed.

ADO.NET

It is possible to use ADO.NET within PowerShell to pass queries, sp, and commands to SQL Server. 

Create a Connection

You simply create an object of System.Data.SqlClient.SqlConnection and pass the connection string that will be used to connect to the given SQL Server instance…don't forget to open it.

Create Command

You have a few options here because the SqlConnection actually contains a method that you can use to create your command object,

ExecuteNonQuery 

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

01.Import-csv C:\InputServer.csv|%{
02.If ((Test-Connection $_.ServerName -count 1 -quiet)) {
03.[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName $_.ServerName Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
04.[string]$XmlData = "<root><cimv2>";
05.$Volumes | % { $XmlData = $XmlData + "<Win32_Volume ServerName=`"" + $_.SystemName + "`" Name=`"" + $_.Name + "`" Label=`"" + $_.Label + "`" DriveLetter=`"" + $_.DriveLetter + "`" Capacity=`"" + $_.Capacity + "`" FreeSpace=`"" + $_.FreeSpace + "`"></Win32_Volume>" };
06.$XmlData = $XmlData + "</cimv2></root>";$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
07.$SqlConnection.ConnectionString = "Server=HQDBSP18;Database=PowerSQL;Integrated Security=TRUE;";
08.$SqlConnection.Open();
09.$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
10.$SqlCommand.CommandTimeout = 120;
11.$SqlCommand.Connection = $SqlConnection;
12.$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N'$XmlData';";
13.$Result = $SqlCommand.ExecuteNonQuery();
14.$SqlConnection.Close();
15.}
16.}

↑ Return to Top



Output

↑ Return to Top




Conclusion

  • It shows the use of ADO.Net class library as an alternative for  invoke-sqlcmd
  • The advantage of this script is that from the point of scheduling. The same script can be scheduled in task scheduler or SQL Agent
  • The data is stored in the repository and it can be used for capacity planning and forecasting 
  • Data purging in place. We can schedule a job to purge the data
  • It's an effort to demonstrate the one other way of gathering important metrics of system measurement

↑ Return to Top


Reference

TechNet Blog

External Blog

↑ Return to Top


See Also