Any way to send email about job is runing forever?
-
Hi Everyone,
How do I monitor my production server job status? Because I have created the job to run Backup at night but sometime job are still running and my server CPU is nearly 100% :w00t: and nobody know what is happening on Server if I am not in the office. [example, they can't run any application or any connection to the server, etc]
My question is, how can I setup the email or SMS to send me a job which are sill running after certain amount of time? So that I can get into the server from anywhere and sort out the problems?
Thanks.
Leo
-
Will this do (changing DB name to your own, and setting this sp as a job)?
CREATE PROCEDURE Monitor_Backups_And_Alert(@Allowed_Threshold INT)
AS
BEGIN
DECLARE @Hours_since_last_backup INT
SELECT @Hours_since_last_backup = ISNULL(STR(ABS(DATEDIFF(hour, GetDate(),MAX(Backup_Finish_Date)))), 'NEVER')
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.BackupSet A
ON A.Database_Name = B.Name AND A.Type = 'D'
WHERE Database_Name IN('Your_DB_Name')
GROUP BY Database_Name
HAVING ISNULL(STR(ABS(DATEDIFF(hour, GetDate(),MAX(Backup_Finish_Date)))), 'NEVER') > 0
IF @Hours_since_last_backup > @Allowed_Threshold
BEGIN
-- Send Email
END
END
-
Be aware that you might have issues with this if the server is at 100% CPU. You might want to set something up with a monitoring solution on the Windows level to note if the server is at 100% or SQL is for more than xx minutes as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply