Check Space for mounted drives

USE [msdb]
GO

/****** Object:  Job [DBA_CheckDriveSpace]    Script Date: 1/15/2016 8:46:13 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 1/15/2016 8:46:13 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DBA_CheckDriveSpace',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'Team_DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CheckDriveSpace]    Script Date: 1/15/2016 8:46:14 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CheckDriveSpace',
@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'###############################################################################
# Check the percentage of free space, including mount points
###############################################################################
# For use in sending alerts via SQL Server Database Mail and may be scheduled
# via a SQL Server Agent job. Create a new Agent job and creat a new step, set
# the Type as PowerShell, edit the code below to put in your server name(s),
# Database Mail profile name, and recipient email address(es), optionally
# change the warning threshold, then paste the code into the Command pane and
# create a schedule.
###############################################################################

# List the servers you want to check for low disk capacities
$ServerArray = @(''AWOTORPRODSQL01'', ''AWOTORPRODSQL02'')
# Server names (not SQL Server instance names) must be in single quotes and
# separated by commas. If the SQL Server Agent service account does not have
# admin permissions on remote servers, you must assign Remote Execute
# permissions to the Agent account on each remote server, and the Agent account
# must be a domain account. To assign Remote Execute, run wmimgmt.msc,
# right-click/Properties, select the Security tab, expand the Root node, select
# the CIMV2 node, click the Security button, add the Agent account and scroll
# down to find and check the box for the "Remote Enable" permission.

import-module "sqlps" -DisableNameChecking

# Set threshold percentage for low disk capacity warnings.
$Threshold = .2
# Threshold value must be between 0 and 1.
# (E.g. ".1" will produce warnings when free space is below 10%.

# Set values for units of measure
[string]$UnitOfMeasure = ''1GB''
$UnitOfMeasureTerm = "GB"
# Use an empty string for bytes or KB/MB/GB/TB/PB
# (KB-PB are constants in PowerShell)

ForEach ($ServerName in $ServerArray)
{
  $Volumes = Get-WmiObject -namespace "root/cimv2" -computername $ServerName -query "SELECT Name, Capacity, FreeSpace FROM Win32_Volume WHERE DriveType = 2 OR DriveType = 3"
 
  ForEach ($Volume in $Volumes)
  {
    [string]$DriveType = Switch($Volume.DriveType)
    {
      0{''Unknown''}
      1{''No Root Directory''}
      2{''Removable Disk''}
      3{''Local Disk''}
      4{''Network Drive''}
      5{''Compact Disk''}
      6{''RAM Disk''}
      default {''Unknown''}
    }
    [string]$Drive = "Drive: {0}" -f$Volume.Name
    [string]$Capacity = "Capacity: {0} {1}" -f[System.Math]::Round(($Volume.Capacity / $UnitOfMeasure),0), $UnitOfMeasureTerm
    [string]$FreeSpace = "Free Space: {0} {1}" -f[System.Math]::Round(($Volume.FreeSpace / $UnitOfMeasure),0), $UnitOfMeasureTerm
    [string]$PercentFree = "Percent Free Space: " + [System.Math]::Round(($Volume.FreeSpace / $Volume.Capacity), 2)*100 + "%"
   
    # Send an email alert via Database Mail if a disk is below the warning threshold
    If ($Volume.FreeSpace / $Volume.Capacity -lt $Threshold)
    {
      $Qry = "EXECUTE msdb.dbo.sp_send_dbmail @profile_name = ''DB Errors'',
      @recipients = ''Team_DBA2@activenetwork.com'',
      @subject = ''AWOTORPRODSQL Low Disk Capacity Warning'',
      @body_format = ''text'',
      @body = ''WARNING: Low free space on " + $ServerName.ToUpper() + ".
      " + $Drive + "
      " + $Capacity + "
      " + $FreeSpace + "
      " + $PercentFree + "''"
      # Replace with the name of the Database Mail profile name.
      # Replace with who you want the email to go to.
      # Changing the formatting of $Qry will change the format of the email.
     
      Invoke-SqlCmd -query $Qry -ServerInstance AWOTORPRODSQL
      # If the local instance of SQL Server is a named instance, use the
      # "DOMAIN\InstanceName" in place of ''localhost''.
    }

    ###########################################################################
    # If you''d like to get a disk capacity report interactively, comment-out
    # the If statement above and comment-in the following If statement:
   
    If ($Volume.FreeSpace / $Volume.Capacity -lt .1)
    {
      Write-Output "WARNING: Disk capacity is less than 10%"
      Write-Output $Drive
      Write-Output $Capacity
      Write-Output $FreeSpace
      Write-Output $PercentFree
    }
    Else
    {
      Write-Output $Drive
      Write-Output $Capacity
      Write-Output $FreeSpace
      Write-Output $PercentFree
    }
  }
}
',
@database_name=N'master',
@output_file_name=N'V:\Volumes\Backups01\Scripts\CheckSpace.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Every half hour',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=30,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150115,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'dea34bfb-8396-4f14-ba12-0a0b282389fd'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @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:

GO