CAN'T remove "Execute job" from alert in SS 2005?

This is kind of strange. On a SQL 2005 SP2 box, I've set up a few alerts to
monitor tran log growth. If they reach the threshold the alerts will fire
and send emails and kick off a tran log dump job. Now I want to remove the
job by unchecking the "Execute job" box from the "Response" node. after
clicking OK and going back to revisit it, the job is still checked. I've
tested this on a few other SQL instances - all are the same. From my
machine, I tested on a SQL 2000 server and it works as expected.
The alert is scripted below. I have also tried to change @.job_id to null in
a hope that the sproc will make it unchecked. But still the old job exists
in the alert.
EXEC msdb.dbo.sp_add_alert @.name=N'test',
@.message_id=0,
@.severity=0,
@.enabled=1,
@.delay_between_responses=0,
@.include_event_description_in=1,
@.category_name=N'[Uncategorized]',
@.performance_condition=N'SQLServer:Databases|Log File(s) Used Size
(KB)|tempdb|>|100000',
@.job_id=N'4d9d9fde-0fed-4d79-b894-0a04ae0d2b85'
I tend to believe this is a SQL 2005 bug. Has anyone else seen or can anyone
duplicate the same problem?
RickIt is not strange if it's a bug. :-) confirmed for ss2005. someone already
reported in April 2007.
"YPD" <y.ding@.neu.edu> wrote in message
news:C272B7CA-23BD-497A-AACC-C84E60A5030E@.microsoft.com...
> This is kind of strange. On a SQL 2005 SP2 box, I've set up a few alerts
> to monitor tran log growth. If they reach the threshold the alerts will
> fire and send emails and kick off a tran log dump job. Now I want to
> remove the job by unchecking the "Execute job" box from the "Response"
> node. after clicking OK and going back to revisit it, the job is still
> checked. I've tested this on a few other SQL instances - all are the same.
> From my machine, I tested on a SQL 2000 server and it works as expected.
> The alert is scripted below. I have also tried to change @.job_id to null
> in a hope that the sproc will make it unchecked. But still the old job
> exists in the alert.
> EXEC msdb.dbo.sp_add_alert @.name=N'test',
> @.message_id=0,
> @.severity=0,
> @.enabled=1,
> @.delay_between_responses=0,
> @.include_event_description_in=1,
> @.category_name=N'[Uncategorized]',
> @.performance_condition=N'SQLServer:Databases|Log File(s) Used Size
> (KB)|tempdb|>|100000',
> @.job_id=N'4d9d9fde-0fed-4d79-b894-0a04ae0d2b85'
> I tend to believe this is a SQL 2005 bug. Has anyone else seen or can
> anyone duplicate the same problem?
>
> Rick

Cant make alert job work.

sql2k
sp4
Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
checked to fire off a job when it occurs. I then generate a deadlock, but the
job never gets fired off. Some important fun facts:
1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
2; I've tried running different jobs manually. They both work.
On the advice I got last week, I have verified that the Event Log service
and SQL Server Agent are both running. Its probably worth noting though that
while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
Event Viewer.
Any ideas?
TIA, ChrisRYou need to configure 1205 so it is written to the eventlog. In EM, right-click the server, Manage
Messages. Here you search for 1205 and check to write to eventlog. In 2005, you cannot customize
system messages this way, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> sql2k
> sp4
> Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> checked to fire off a job when it occurs. I then generate a deadlock, but the
> job never gets fired off. Some important fun facts:
> 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> 2; I've tried running different jobs manually. They both work.
> On the advice I got last week, I have verified that the Event Log service
> and SQL Server Agent are both running. Its probably worth noting though that
> while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
> Event Viewer.
> Any ideas?
> TIA, ChrisR
>|||I keep trying to add this but it doesnt save it. I add it, hit OK, reopen and
its gone. Im logged in as local admin so I know thats not it. Any ideas?
"Tibor Karaszi" wrote:
> You need to configure 1205 so it is written to the eventlog. In EM, right-click the server, Manage
> Messages. Here you search for 1205 and check to write to eventlog. In 2005, you cannot customize
> system messages this way, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> > sql2k
> > sp4
> >
> > Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> > checked to fire off a job when it occurs. I then generate a deadlock, but the
> > job never gets fired off. Some important fun facts:
> >
> > 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> > 2; I've tried running different jobs manually. They both work.
> >
> > On the advice I got last week, I have verified that the Event Log service
> > and SQL Server Agent are both running. Its probably worth noting though that
> > while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
> > Event Viewer.
> >
> > Any ideas?
> >
> > TIA, ChrisR
> >
>|||Tibor I just realized this is more of a "not write to error log" issue, so I
reposted accordingly, thanks.
"Tibor Karaszi" wrote:
> You need to configure 1205 so it is written to the eventlog. In EM, right-click the server, Manage
> Messages. Here you search for 1205 and check to write to eventlog. In 2005, you cannot customize
> system messages this way, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> > sql2k
> > sp4
> >
> > Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> > checked to fire off a job when it occurs. I then generate a deadlock, but the
> > job never gets fired off. Some important fun facts:
> >
> > 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> > 2; I've tried running different jobs manually. They both work.
> >
> > On the advice I got last week, I have verified that the Event Log service
> > and SQL Server Agent are both running. Its probably worth noting though that
> > while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
> > Event Viewer.
> >
> > Any ideas?
> >
> > TIA, ChrisR
> >
>

Cant make alert job work.

sql2k
sp4
Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
checked to fire off a job when it occurs. I then generate a deadlock, but th
e
job never gets fired off. Some important fun facts:
1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
2; I've tried running different jobs manually. They both work.
On the advice I got last week, I have verified that the Event Log service
and SQL Server Agent are both running. Its probably worth noting though that
while I do see the deadlock in the SQL Error Log, it doesnnt show up in the
Event Viewer.
Any ideas?
TIA, ChrisRYou need to configure 1205 so it is written to the eventlog. In EM, right-cl
ick the server, Manage
Messages. Here you search for 1205 and check to write to eventlog. In 2005,
you cannot customize
system messages this way, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
> sql2k
> sp4
> Howdy all. I set up an Alert for deadlocks (# 1205) and have the checkbox
> checked to fire off a job when it occurs. I then generate a deadlock, but
the
> job never gets fired off. Some important fun facts:
> 1; I ran dbcc traceon with 1204 and 1205 to log deadlocks.
> 2; I've tried running different jobs manually. They both work.
> On the advice I got last week, I have verified that the Event Log service
> and SQL Server Agent are both running. Its probably worth noting though th
at
> while I do see the deadlock in the SQL Error Log, it doesnnt show up in th
e
> Event Viewer.
> Any ideas?
> TIA, ChrisR
>|||I keep trying to add this but it doesnt save it. I add it, hit OK, reopen an
d
its gone. Im logged in as local admin so I know thats not it. Any ideas?
"Tibor Karaszi" wrote:

> You need to configure 1205 so it is written to the eventlog. In EM, right-
click the server, Manage
> Messages. Here you search for 1205 and check to write to eventlog. In 2005
, you cannot customize
> system messages this way, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
>|||Tibor I just realized this is more of a "not write to error log" issue, so I
reposted accordingly, thanks.
"Tibor Karaszi" wrote:

> You need to configure 1205 so it is written to the eventlog. In EM, right-
click the server, Manage
> Messages. Here you search for 1205 and check to write to eventlog. In 2005
, you cannot customize
> system messages this way, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:EA439440-08D7-4BBA-8EA5-E69C4DEC6C44@.microsoft.com...
>