question

Maheshreddy avatar image
Maheshreddy asked

How to configure jobs in sql server2008 and the output should notify to user mailid

Hello All, we have 25 Sql 2008 servers,we dont have any monitoring tools to monitor alerts like low disk space on all drives,log file is full,job success or failure,database is in suspect mode or offline,services are stopped or srarted etc. Please any one help me how to configure those all jobs and the output (i.e suceess or failure) should notify to the user mailid. Thanks...
sql-server-2008sql-server-2008-r2monitoring
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DharmanDave avatar image
DharmanDave answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You can also create maintenance plan for monitoring the status/consistency of the databases. Keep one of the steps to send email notification in case of failure action.
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
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 $_}
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.