By SQL Professionals | October 10, 2014

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.

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [dbo].[DiskVolume]
(
	 [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];
GO
SET ANSI_PADDING OFF;
GO

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

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] 
	(
			 [CheckDate]
			,[Name] 
			,[Label] 
			,[DriveLetter] 
			,[CapacityKB] 
			,[FreeSpaceKB] 
	)
	SELECT	 [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 
			 (
				 [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;
GO

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.

SET NOCOUNT ON;

DECLARE @XmlData AS XML = N''
DECLARE @VarData AS VARCHAR(MAX) = '';

--Create xml structured string to test our insert procedure
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';
SET @VarData = @VarData + N'';

--Cast our xml structured string to an xml type
SET @XmlData = CAST(@VarData AS XML);

--Call our procedure
EXECUTE [dbo].[InsertDiskVolume] @XmlData;

--Was the data inserted?
SELECT *
FROM [dbo].[DiskVolume];

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.

[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName "." Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
[string]$XmlData = ""; 
$Volumes | % { $XmlData = $XmlData + "" }; 
$XmlData = $XmlData + "";
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = "Server=;Database=;Integrated Security=TRUE;"; 
$SqlConnection.Open();
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandTimeout = 120;
$SqlCommand.Connection = $SqlConnection;
$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N'$XmlData';";
$Result = $SqlCommand.ExecuteNonQuery();
$SqlConnection.Close();

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.

USE [msdb]; 
GO 
BEGIN TRANSACTION; 
	DECLARE @ReturnCode AS INT = 0; 
	DECLARE @jobId AS BINARY(16);
	
	IF NOT EXISTS (SELECT [name] FROM [msdb].[dbo].[syscategories] WHERE [name] = N'[Uncategorized (Local)]' AND [category_class] = 1) 
	BEGIN 
		EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_category] 
				@class=N'JOB', 
				@type=N'LOCAL', 
				@name=N'[Uncategorized (Local)]'; 
				
		IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 
	END 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_job] 
			@job_name=N'Collect Disk Space Data', 
			@enabled=1, 
			@notify_level_eventlog=0, 
			@notify_level_email=0, 
			@notify_level_netsend=0, 
			@notify_level_page=0, 
			@delete_level=0, 
			@description=N'No description available.', 
			@category_name=N'[Uncategorized (Local)]', 
			@owner_login_name=N'sa', 
			@job_id = @jobId OUTPUT; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobstep] 
			@job_id=@jobId, 
			@step_name=N'Query WMI', 
			@step_id=1, 
			@cmdexec_success_code=0, 
			@on_success_action=1, 
			@on_success_step_id=0, 
			@on_fail_action=2, 
			@on_fail_step_id=0, 
			@retry_attempts=0, 
			@retry_interval=0, 
			@os_run_priority=0, 
			@subsystem=N'PowerShell', 
			@command=N'[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName "$(ESCAPE_NONE(MACH))" Win32_Volume -Filter "DriveType = 3 AND Label != ''System Reserved''";
	[string]$XmlData = ""; $Volumes | % { $XmlData = $XmlData + "" }; $XmlData = $XmlData + "";
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
	$SqlConnection.ConnectionString = "Server=$(ESCAPE_NONE(SRVR));Database=;Integrated Security=TRUE;"; 
	$SqlConnection.Open();
	$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
	$SqlCommand.CommandTimeout = 120;
	$SqlCommand.Connection = $SqlConnection;
	$SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N''$XmlData'';";
	$Result = $SqlCommand.ExecuteNonQuery();
	$SqlConnection.Close();', 
			@database_name=N'master', 
			@flags=0; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_update_job] 
			@job_id = @jobId, 
			@start_step_id = 1; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobschedule] 
			@job_id=@jobId, 
			@name=N'Daily - Every 24 hour(s)', 
			@enabled=1, 
			@freq_type=4, 
			@freq_interval=1, 
			@freq_subday_type=1, 
			@freq_subday_interval=0, 
			@freq_relative_interval=0, 
			@freq_recurrence_factor=0, 
			@active_start_date=20140707, 
			@active_end_date=99991231, 
			@active_start_time=0, 
			@active_end_time=235959; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

	EXECUTE @ReturnCode = [msdb].[dbo].[sp_add_jobserver] 
			@job_id = @jobId, 
			@server_name = N'(local)'; 

	IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback; 

COMMIT TRANSACTION; 

GOTO EndSave 

QuitWithRollback: 
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION; 

EndSave:

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.

.

Interested in getting your SQL Server Health Check or learning more about what it involves? Don’t hesitate to reach out; Give us a call at 1-844-SQL-PROS or shoot us an email. We’re more than happy to answer any questions.