Hi Friends, 

I need to monitor if SQL agent job is running more than 3 hours. If some job is running for more than 3 hours then fires an alert.

Please help me how I can achieve this by SQL scirpt

I think this may work for you, I checked it for syntax in SQL 2005.

You can set it up in a SQL job that runs every minute.

 It sends an e-mail to an operator instead of fires an alert, but you should be able to change that easily.

Use MSDB

GO

select * from sysjobhistory where run_status = 4 and run_duration > 030000

IF @@ROWCOUNT > 0

EXECUTE msdb.dbo.sp_notify_operator @name=N'Operator Name',@body=N'Job running for more than 3 hours'