SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 finding orphan SQL notification
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hommer
Aged Yak Warrior

766 Posts

Posted - 01/16/2014 :  10:44:00  Show Profile  Reply with Quote
Hi,
I need some help on finding the process that sending me this phantom/orphan SQL notification email.
It was created by me, most likely through a Maintenance Plan or Agent Job awhile back.
I also could see them in msdb sysmail_sentitems system view.
However, I couldn’t nail it down in order to turn it off.
Here is the detail of the email, but I did not find any failed job in history or any logs.
And this is SQL 2008 R2. Thanks!

----
From: Me
To: myself
Subject: OurApp Weekly DBCC Failure
Message: OurApp Weekly DBCC Failure Check Logs
Sent: every Sun 12:00 AM
----

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 01/16/2014 :  12:22:16  Show Profile  Reply with Quote
Does the agent job history give you any indication? Looking at the jobs that are scheduled to run on Sundays at 12:00 AM perhaps would point you in the righ tdirection.
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 01/17/2014 :  09:08:55  Show Profile  Reply with Quote
I found 2 agent jobs @12.

But they did not fail, and don't have steps running DBCC, and most importantly do not have alert/notification as I have described.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/20/2014 :  08:53:11  Show Profile  Reply with Quote
SQL Agent and SQL Log files any help (what do they have recorded for Sun 12:00 am)?

I doubt SQL Agent log file will help, from memory it only records things that go wrong, but SQL Log often records successful actions too - might just show up something <FingersCrossed!>

Is there a way to interrogate MSDB to find what things could issue a schedule at Midnight? That might point to a schedule, and from there a task associated with it, which is outside the scope of normal jobs perhaps??

You sure you are looking at the right server? {;)]
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1596 Posts

Posted - 01/21/2014 :  19:05:13  Show Profile  Reply with Quote
I haven't used this is quite a while but it might lead you to the source (it's supposed to find a string within the jobs):
use MSDB;

set NoCount ON
set DateFirst 7		-- 7 (Default) ==> Sunday is the first day of the week
--select DatePart(WeekDay, GetDate())

declare
	@match	nvarchar(max),
	@delta	int

select @match = N'OurApp Weekly DBCC Failure'  --<< << << CHANGE THIS AS DESIRED !!!

select
	@@SERVERNAME Server, 
	j.name job_name, 
	js.step_id,
	js.step_name,
	js.command
from
	msdb.dbo.sysjobs j
	inner join msdb.dbo.sysjobsteps js
		on	js.job_id = j.job_id 
where
	js.command like N'%' + @match + N'%'
or	js.step_name like N'%' + @match + N'%'
or	j.name like N'%' + @match + N'%'
order by 
	job_name,
	step_id


=================================================
A man is not old until regrets take the place of dreams. - John Barrymore

Edited by - Bustaz Kool on 01/21/2014 19:08:05
Go to Top of Page

Hommer
Aged Yak Warrior

766 Posts

Posted - 01/28/2014 :  14:17:12  Show Profile  Reply with Quote
Thank you for all the replies!

Sorry for the late reply.

As you may have imagined, the issue is bugging me, but it is not a high priority item on my list.

I tried everything suggested here, including digging around other servers that have the same app, and running the script Bustaz Kool's posted, but found nothing.

I even created an outlook auto process rule to put the weekly notifications into a deleted folder, (hiding them under the carpet:).

Edited by - Hommer on 01/28/2014 14:23:10
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000