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 ProductLevel Edition EngineEdition

10.0.2531.0 SP1 Enterprise Edition (64-bit) 3

In trying to execute the following powershell-script:

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=ASD-SQL-62\LIVE01BMHUB; 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:

/* Use ALTER so as not to destroy any permissions - just in case */ CREATE PROCEDURE dbo.sp_diskspace @performAggregation BIT = 0 /***********************************************

Author : Unknown - rewritten by xxx to support new SQL2008 Job Created : Unknown - rewritten March 2010 Purpose : Return latest disk space info SQL Svr : 2005, 2008

Testing : EXEC dbo.sp_diskspace

***********************************************/ AS /* Displays the free space,free space percentage plus total drive size for a server */ SET NOCOUNT ON;

/* 20100414 : RD : introduced aggregation to keep number of tables rows down */ DECLARE @aggregrateDate DATETIME

IF @performAggregation <> 1 BEGIN WITH Latest (drive, MeasurementDate) AS ( SELECT drive, MAX(MeasurementDate) FROM dbo.DiskSpace GROUP BY drive )

  SELECT 
     DS.drive,
     DS.[free(mb)],
     DS.[total(mb)],
     DS.[free(%)]
  FROM dbo.DiskSpace DS
     JOIN Latest L
        ON L.drive = DS.drive
        AND L.MeasurementDate = DS.MeasurementDate 
  ORDER BY
     DS.drive

END ELSE 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!

@user1005 - is powershell a requirement for this or would you be equally as satisfied with the vbscript equivalent of this?

Bruce · Answer 1 · 2011-03-11 14:07:45Z

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

Eric Humphrey - lotsahelp · Answer 2 · 2011-02-23 14:17:53Z

up vote 1 down vote

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.

edited Feb 23 '11 at 14:17

answered Feb 23 '11 at 14:09

Eric Humphrey - lotsahelp
3,4251621

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? – user1005 Feb 28 '11 at 11:57

add comment (requires an account with 50 reputation)

Eric Humphrey - lotsahelp · Answer 3 · 2011-03-11 19:54:15Z

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()

asked	2 years ago
viewed	873 times
active	2 years ago

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

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged sql-server powershell or ask your own question.

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

3 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-server powershell or ask your own question.

Related