question

Hi... You will have to follow the steps as indicated below: 1. Configure a mail account and mail profile in the 'Database Mail' option under 'Management'. Specify a valid SMTP server which can be used for sending out mails. you can do it using the wizard. 2. Create an operator in the SQL Server Agent. Specify the mail ids of the people you want to notify with ';' separator. 3. For Success action you can specify the last step of the job as an sql query which executes the 'sp_send_dbmail' procedure. refer this link for options http://msdn.microsoft.com/en-us/library/ms190307.aspx 4. For failure action, you can go the the notifications panel on the left in the job properties dialog--> select the operator from drop down menu --> select 'on failure action' from the second drop down menu.

I would suggestion doing some research on Google/Bing. Overall what you are looking for is just a set of small little scripts that pull some information, analyze that information, and then do something about it based on your criteria. Since you are working with SQL Server 2008 R2 I would suggest you look into PowerShell. If you google for something like "PowerShell script to monitor disk space" or "PowerShell scripts to monitor log file free space" and you will likely find a script to do just what you want. To start you off with something that is PowerShell based, this will return some basic information about failed jobs found on a SQL Server instance. This is one I use when needed but you could adjust it for an automated job I am sure:

# Load SMO assembly
[void][System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO' )

# function to get failed job list
function Get-FailedJobs ($server)
{
 foreach ($s in $server)
 {
   $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $s
   $jobList = $srv.JobServer.Jobs | 
           Where {$_.LastRunOutCome -eq "Failed" -AND $_.NextRunDate -gt (Get-Date)} | 
           Select Name, OwnerLoginName, LastRunDate, NextRunDate
    if($jobList)
    {
     Write-Host "Server Name: $s" -Foreground Red
    $jobList
    }
 }
} #end function Get-FailedJobs

You would call this like:

# for default instance
Get-FailedJobs MyServer

# for named instance
Get-FailedJobs MyServer\InstanceName

# to get back results on multiple servers
$serverList = "MyServer1","MyServer2","MyServer\Instance1","MyServer\Instance2"
$serverList | ForEach {Get-FailedJobs $_}