This article shows one possible solution for monitoring disk space usage using SQL Server and a simple PowerShell script executed via SQL Agent Job.

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 here because I have other solutions that monitor SQL Server table and index growth which also stores measurements in units of kb.

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.

Reference: http://msdn.microsoft.com/en-us/library/ms187367.aspx

So, now that we have our table and our insert procedure defined, the next thing to do is to test the insert procedure by passing in an XML parameter just as our PowerShell script would. I found this step to be a very helpful in the development process because the XML data needs to adhere to a specific format in the PowerShell code.

To test the procedure I have created the following T-SQL code to build an XML structured string, CAST it is as an XML data type and then call the procedure and see if the table was populated with any records.

If everything was done correctly, you should have results similar to the following screenshot:

screenshot_01_monitoring-disk-space-sql-server-powershell

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 #6 where you will need to change the connection string parameters and line #11 where you will need to changed the procedure name if needed.

The last step is to create a SQL Agent Job to run the PowerShell script on a recurring schedule. The following code will create such a job that runs every 24 hours and I’ve highlighted a few lines where changes have been made to the PowerShell code from above:

Line #45 is highlighted because I’m using the SQL Agent Token “MACH” and Escape Macro for the “Computer Name” and added a an extra set of single-quotes around “System Reserved”.

Line #48 is highlighted because I’m using the SQL Agent Token “SRVR” and Escape Macro for the Server Name running SQL Server. If the SQL Server instance is a named instance, this includes the instance name. You will still need to specify the appropriate database name and security options for the connection string.

Line #53 is highlighted because, again, you need to make sure the correct procedure name is specified and I’ve also added an extra set of single-quotes around the $XmlData PowerShell variable.

Comments or suggestions are always welcome. The next blog post will be a follow-up on generating a weekly report and low-disk space alert emails. Please check back at a later time or follow us on twitter.

.