Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Question

• Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (VM)
• Microsoft Server 2008 R2 Standard

ProductVersion 10.0.2531.0
ProductLevel SP1
Edition Enterprise Edition (64-bit)
EngineEdition 3

In trying to execute the following powershell-script...

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=localhost\myServer; Initial Catalog=SysAdmin; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {
 $Name = $_.Name.substring(0,1)
 $FreeSpace = $_.FreeSpace
 $Size = $_.Size
 $cmd.CommandText = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"
 $cmd.ExecuteNonQuery()
 $cmd.CommandText = "EXEC dbo.sp_diskspace @performAggregation=1"
 $cmd.ExecuteNonQuery()
}
$conn.Close()

...I get the following error

Message Executed as user: Domain\SqlSrvAgentSer.

A job step received an error at line 7 in a PowerShell script. The corresponding line is 'gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near ','." Incorrect syntax near ','. '. Process Exit Code -1. The step failed.

Additional Information

CREATE PROCEDURE dbo.sp_diskspace
 @performAggregation BIT = 0
AS
SET NOCOUNT ON;
DECLARE @aggregrateDate DATETIME
BEGIN
 SET @aggregrateDate = DATEADD(month, -1, GETDATE())
 SET @aggregrateDate = DATEADD(dd, DATEDIFF(dd, 0, @aggregrateDate), 0)
 INSERT INTO dbo.diskspace (
 drive,
 MeasurementDate,
 [free(bytes)],
 [total(bytes)],
 isAggregated)
 SELECT 
 drive,
 DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0), 
 AVG([free(bytes)]),
 AVG([total(bytes)]),
 1
 FROM 
 dbo.diskspace
 WHERE
 MeasurementDate < @aggregrateDate
 AND isAggregated <> 1
 GROUP BY
 drive,
 DATEADD(dd, DATEDIFF(dd, 0, MeasurementDate), 0)
 IF @@ERROR = 0 and @@ROWCOUNT > 0
 BEGIN
 DELETE FROM dbo.diskspace 
 WHERE MeasurementDate < @aggregrateDate 
 AND isAggregated <> 1
 IF @@ERROR = 0
 BEGIN
 RAISERROR('sp_diskspace : aggregation complete', 0, 1)
 END
 END
END

Any help on this would be greatly appreciated!

• john.da.costa: @user1005 - is powershell a requirement for this or would you be equally as satisfied with the vbscript equivalent of this?
• Marcello Miorelli: @user1005 would you share the code of sp_diskspace. looks interesting. : "EXEC dbo.sp_diskspace @performAggregation=1"

Answer 1

Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little. Add this after the line setting $size = $_.Size

 $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
 $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate for your system
 $cmd.CommandText = $sql

To me using the extra variable but getting an easier to read insert command is a good tradeoff and the debug.sql file makes finding sql errors much easier. HTH

Answer 2

You could use parameters to solve this problem with the added benefit of execution plan reuse. Set the data types to match database.

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ASD-SQL-62\LIVE01BMHUB; Initial Catalog=SysAdmin; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES (@drive, @freespace, @size)"
$cmd.Parameters.Add("@drive", [System.Data.SqlDbType]::VarChar, 1)
$cmd.Parameters.Add("@freespace", [System.Data.SqlDbType]::Int)
$cmd.Parameters.Add("@size", [System.Data.SqlDbType]::Int)
$cmd2 = $conn.CreateCommand()
$cmd2.CommandText = "dbo.sp_diskspace"
$cmd2.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd2.Parameters.Add("@performAggregation", [System.Data.SqlDbType]::Int).Value = 1
gwmi -query "select * from Win32_LogicalDisk where DriveType=3" | select Name, FreeSpace, Size | foreach {
 $cmd.Parameters["@drive"].Value = $_.Name.substring(0,1)
 $cmd.Parameters["@freespace"].Value = $_.FreeSpace
 $cmd.Parameters["@size"].Value = $_.Size
 $cmd.ExecuteNonQuery()
 $cmd2.ExecuteNonQuery()
}
$conn.Close()

Answer 3

In the following part of the INSERT query:

('"+ $Name + "', " + $FreeSpace + ", " + $Size + ")"

If either $FreeSpace or $Size -eq $null, then it won't properly complete the query string. Either use command parameters just as you would in .NET (best method) or check for $null before insert.

• Nico Botes: Thanks Eric. I've had a look at this, but handling the NULLs is not going to help as I rely on information returned to update our monitoring dashboard. Is there any reason by the WMI-select query would return NULL values for the above-mentioned columns?