SQL Agent

• Imke Cronje

  SSCarpal Tunnel

  Points: 4826

  More actions

  February 27, 2007 at 1:44 am

  #116241

  Hi Guys,

  I have the following query:

  CREATE

  proc [dbo].[sp__TNGAlert]

  @JobName NVARCHAR(4000),

  @alert

  VARCHAR(50)

  as

  SET

  NOCOUNT ON

  --

  DECLARE

  @AlertDir NVARCHAR(4000)

  DECLARE

  @ServerName VARCHAR(50)

  DECLARE

  @FailureDate VARCHAR(50)

  DECLARE

  @Error NVARCHAR(4000)

  DECLARE

  @cmd SYSNAME

  --

  SET

  @AlertDir = ' >> \\BEPSQL01\SQLG\TNGLog\Alerts.txt'

  SET

  @ServerName = @@ServerName

  SET

  @FailureDate = (select getdate())

  SET

  @Error = @ServerName + ' - ' + @JobName + ' ' + @alert + ' on ' + @FailureDate + @AlertDir

  SET

  @cmd = 'echo ' + @error

  --

  EXEC

  master.dbo.xp_cmdshell @cmd

  This script was created to send alerts via email and txt message when a particular job on a server fails.

  I am planning to create a DTS package to display the list of sevrers in a csv file where the agent is running and also where the agent is not running. I have to figure out a way, if the SQL agent is not running the an alert needs to be sent via email and txt message. Can anyone pls assist me on this?

  The following script that I a using will be used in the DTS package to display whether the service is running.

  create proc sp__SQLAgent

  As

  select @@Servername as ServerName,Program_name,

  Case when program_name = 'SQLAgent - Generic Refresher'

  then 'SQL Agent Running'

  else 'SQL Agent Not running'

  end as Process

  from sysprocesses where program_name Like '%Ref%'

  Regards

• Eric Peterson

  Hall of Fame

  Points: 3192

  More actions

  February 27, 2007 at 9:03 am

  #692087

  What to do about SQL Error Reporting when the agent or SQL Server is down?

  I tried a number of ways to solve this and found a third party product that will monitor not only the SQL Server status but the SQL Server Agent and network as well. 

  It is IPMonitor Version 8 and I think you can about $1000.  Even better you can download a 21 day trial for free.  http://www.ipmonitor.com/

  This product monitors my disk space, network, SQL Server, SQL Server Agent and can even run a query into my database to make sure that everything is running.   When it encounters a problem, the IPMonitor software sends me an email.   For the initial price we are monitoring 30+ machines on several points. 

  My 2 cents

  Eric