SQL Job

Question

Post reply

SQL Job

smthembu

Hall of Fame

Points: 3464
More actions
April 20, 2010 at 12:02 am

#156883

Hi All
I need help, i have a job i created for test purposes using database mail, so i had to change few things on the job then i disabled the job including the scheduling but i still get alerts even if i have deleted the job, why that?
Please help
Tx!
It's better to fail while trying, rather than fail without trying!!!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

OomBoom Hall of Fame Points: 3481 More actions · Answer 1

1. Where did you setup the Alert?

2. What version suppose 2005

3. Check - select * from sysalerts in msdb

4. Is it perhaps logshipping

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

smthembu Hall of Fame Points: 3464 More actions · Answer 2

Tx, it's a sql script that execute a database mail command if the condition is not met, then sends me an email scheduled as a job but now the job is deleted but i dont understand why i'm still getting mail. i've restarted sqlagent but still the same.

It's better to fail while trying, rather than fail without trying!!!

OomBoom Hall of Fame Points: 3481 More actions · Answer 3

Try this script and see if the job is still in the table:

select * from sysjobs

What kind off condition are you looking at. Here I am thinking that there could be another alert setup in side a Job.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

smthembu Hall of Fame Points: 3464 More actions · Answer 4

Pls see below script but was scheduled as a job to run every hour

DECLARE @CallPresentIndicator Bit -- bit data type is to indicate true or false. 0 is "False" and 1 is "True"

IF (SELECT COUNT (*) FROM TRANS

WHERE CALL_START >= DATEADD(HH,-1,GETDATE())

AND CALL_START <= GETDATE ()) >0

BEGIN

SET @CallPresentIndicator = 1 -- this means TRUE

PRINT 'IT IS WORKING'

END

ELSE

-- Create the body of the email

IF @CallPresentIndicator = 1

BEGIN

DECLARE @body1 nvarchar(2000)

SET @body1 =

'<html>

<head>

</head>

<tr>

</tr>

<tr>

<td align="center" colspan="2" style="font-size:16;color:LIME"><b>Automated Database Mail Alert</b></td>

</tr>

<tr>

<td align="center">' + convert(char(19),getdate(),20) + '</td>

</tr>

<tr>

<td align="left">SERVER:</td>

<td align="center">' +@@SERVERNAME+ '</td>

</tr>

<tr>

<td align="left">SEVERITY:</td>

<td align="center" style="color:RED"><b>WARNING!</b></td>

</tr>

<tr>

<td align="left">SUBSYSTEM:</td>

<td align="center">Database Update</td>

</tr>

<tr>

<td align="left">DESCRIPTION:</td>

Please check your Application

</td>

</tr>

</table>

</body>

</html>'

-- Concatenate subject

DECLARE @subj nvarchar(50)

SET @subj = @@SERVERNAME + N': Check DB'

-- send the email

EXEC msdb.dbo.sp_send_dbmail @recipients='email',

@subject = @subj,

@body = @body1,

@body_format = 'HTML',

@profile_name = 'DBA Admin Profile'

END

It's better to fail while trying, rather than fail without trying!!!

OomBoom Hall of Fame Points: 3481 More actions · Answer 5

Hi

The next step is to check the sysprocess table. Look for something like "SQLAgent - TSQL JobStep............"

"select * from sysprocesses where spid > 50"

dbcc inputbuffer(spid) - to see code that is running in the process.

If you find the code then you can kill that process.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

OomBoom Hall of Fame Points: 3481 More actions · Answer 6

Sorry here is the full select

use master

select * from sysprocesses where spid > 50 and program_name like 'SQLAgent - TSQL JobStep%'

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

smthembu Hall of Fame Points: 3464 More actions · Answer 7

Tx 4 yr help i'm sorted

It's better to fail while trying, rather than fail without trying!!!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

smthembu (4/20/2010)
Tx 4 yr help i'm sorted

If you solved your problem, it would be nice if you posted what you did. Someone else may have a similar problem and knowing what someone else did could be helpful.

smthembu Hall of Fame Points: 3464 More actions · Answer 9

I used tvantonder-992012 suggestions as posted, also checked the msdb.dbo.sysjobactivity table and i could see the job also joined the sysjob table to get the full desc of the job id's.

It's better to fail while trying, rather than fail without trying!!!