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!

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.