Alerts Not Working

I defined an Alert in Enterprise Manager for Severity 19 - Fatal Error In Resource. The alert is enabled, the type is "sql server event alert" and the alert is defined against a specific database. SQLSERVERAGENT and SQL Mail session are both started. I test the alert using the following statement in the specified database.

raiserror (50001,19,1) with log

A message is placed in the SQL Server log, however no alert is fired. The alert history indicates the alert has never occurred. What am I missing?

Thanks, DaveHere's an update.

I refreshed EM earlier in the day and still saw no history. This time I disconnected and reconnected the instance. For some reason I have to do this in certain situations. After reconnecting I noticed a history existed under a demo for Severity 19. I didn't realize the demos were enabled. I just disabled the demo alert and all appears to be working.

Thanks, Dave|||all the demos are enabled for each of the severity levels if you are defining errors soley based on severity they will as you found out hit first
also ...
[BOL] Says

Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.

you might want to consider refining your alert to fire on the event id's that are acutally being thrown..
just a suggest

by the way good debugging on your part.|||The error message I'm seeing is being generated by a vendor's software package.

Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration..

There are multiple performance problems associated with this application. I suspect the error above is connected to many of the problems. I don't believe the database is damaged. I'm guessing it's just a matter of poorly written code. Now I have to figure out how to setup Profiler to look for this error.

Thanks, Dave|||http://www.sql-server-performance.com/deadlocks.asp to prevent deadlocks and error defined.

Ensure to run the statement in batches if it is affecting large number of rows. Another option is to specify a hint like table or page lock so that the number of locks can be reduced.

What are lock settings defined at SQL server?|||The lock setting is still at the default of 0. I'm not sure what code is causing this problem to occur. I need to setup Profiler to trap the logic. I just setup Profiler to look for Severity 19, but the filter does not appear to be working. I'm still seeing all server activity. Am I missing something?

- Filters
- Severity
- Equals 19

Dave|||Oh, then follow this link http://www.sql-server-performance.com/blocking.asp to monitor the blocking and capture the trace.|||Not missing much about profiler. Any null value is returned as a valid hit. You may want to just run profiler to a table with the SQL statement complete, RPC complete, and some of the error events getting logged. Once you have a pile of data in a table, you can run queries off of that for severity 19, then try to work back via the identity column to figure out what statement did it.|||Can you tell Profiler to ignore NULL?

I'll give your suggestion a try.

Thankssql

alerting for severity levels and not through SQL Alerts

Right now, we do not use the SQL Server Alerts since we
do not have an Outlook profile setup on our SQL Servers.
So is there any other way, i could be alerted for Sev
levels 17-25 thru some sproc that i can call
periodically ?
Also is there a way one can implement this with Site
scope tool ?
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
One option is mentioned at:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?
|||You can still use alerts if you want. You could have the alert start a SQL
Agent job when it is fired that sends a email via CDOSYS of CDONTS. Check
out the KB article:
http://support.microsoft.com/default...b;EN-US;312839
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?
sql

alerting for severity levels and not through SQL Alerts

Right now, we do not use the SQL Server Alerts since we
do not have an Outlook profile setup on our SQL Servers.
So is there any other way, i could be alerted for Sev
levels 17-25 thru some sproc that i can call
periodically ?
Also is there a way one can implement this with Site
scope tool ?> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
One option is mentioned at:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?|||You can still use alerts if you want. You could have the alert start a SQL
Agent job when it is fired that sends a email via CDOSYS of CDONTS. Check
out the KB article:
http://support.microsoft.com/defaul...kb;EN-US;312839
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?

alerting for severity levels and not through SQL Alerts

Right now, we do not use the SQL Server Alerts since we
do not have an Outlook profile setup on our SQL Servers.
So is there any other way, i could be alerted for Sev
levels 17-25 thru some sproc that i can call
periodically ?
Also is there a way one can implement this with Site
scope tool ?> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
One option is mentioned at:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?|||You can still use alerts if you want. You could have the alert start a SQL
Agent job when it is fired that sends a email via CDOSYS of CDONTS. Check
out the KB article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;312839
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ronald James" <anonymous@.discussions.microsoft.com> wrote in message
news:7bc501c43185$603899b0$a401280a@.phx.gbl...
> Right now, we do not use the SQL Server Alerts since we
> do not have an Outlook profile setup on our SQL Servers.
> So is there any other way, i could be alerted for Sev
> levels 17-25 thru some sproc that i can call
> periodically ?
> Also is there a way one can implement this with Site
> scope tool ?

Alert is not Firing

I have configured the alert of
Type SQL Server event alert
for the severity 016-Miscellaneous user Error
DB is all Database
In response tab, I have confiured the job.
I have created one procedure in pubs dB like
create proc test
as
begin
raiserror('going to configure ',16,1)
end
After that I have executed this Stored Procedure.
exec test.
My understanding is that now the alert should be fired and then it
should execute a job. But it is not at all happening. I.e The alert is
not at all firing.
Kindly give some solution.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks and regards
Praveen.AAlert firing is based on the eventlog. Make sure you write to eventlog. Use either WITH LOG for
RAISERROR or (preferably) design your own messages (sp_addmessage) and specify there to always write
to eventlog.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Praveen" <apveen@.gmail.com> wrote in message
news:1127218958.512008.318710@.g14g2000cwa.googlegroups.com...
>I have configured the alert of
> Type SQL Server event alert
> for the severity 016-Miscellaneous user Error
> DB is all Database
> In response tab, I have confiured the job.
> I have created one procedure in pubs dB like
> create proc test
> as
> begin
> raiserror('going to configure ',16,1)
> end
> After that I have executed this Stored Procedure.
> exec test.
> My understanding is that now the alert should be fired and then it
> should execute a job. But it is not at all happening. I.e The alert is
> not at all firing.
> Kindly give some solution.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Thanks and regards
> Praveen.A
>|||Thanks a lot Tibor.
I got a solution. Alert is firing now.

Alert is not Firing

I have configured the alert of
Type SQL Server event alert
for the severity 016-Miscellaneous user Error
DB is all Database
In response tab, I have confiured the job.
I have created one procedure in pubs dB like
create proc test
as
begin
raiserror('going to configure ',16,1)
end
After that I have executed this Stored Procedure.
exec test.
My understanding is that now the alert should be fired and then it
should execute a job. But it is not at all happening. I.e The alert is
not at all firing.
Kindly give some solution.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks and regards
Praveen.A
Alert firing is based on the eventlog. Make sure you write to eventlog. Use either WITH LOG for
RAISERROR or (preferably) design your own messages (sp_addmessage) and specify there to always write
to eventlog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Praveen" <apveen@.gmail.com> wrote in message
news:1127218958.512008.318710@.g14g2000cwa.googlegr oups.com...
>I have configured the alert of
> Type SQL Server event alert
> for the severity 016-Miscellaneous user Error
> DB is all Database
> In response tab, I have confiured the job.
> I have created one procedure in pubs dB like
> create proc test
> as
> begin
> raiserror('going to configure ',16,1)
> end
> After that I have executed this Stored Procedure.
> exec test.
> My understanding is that now the alert should be fired and then it
> should execute a job. But it is not at all happening. I.e The alert is
> not at all firing.
> Kindly give some solution.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Thanks and regards
> Praveen.A
>
|||Thanks a lot Tibor.
I got a solution. Alert is firing now.
sql

Alert is not Firing

I have configured the alert of
Type SQL Server event alert
for the severity 016-Miscellaneous user Error
DB is all Database
In response tab, I have confiured the job.
I have created one procedure in pubs dB like
create proc test
as
begin
raiserror('going to configure ',16,1)
end
After that I have executed this Stored Procedure.
exec test.
My understanding is that now the alert should be fired and then it
should execute a job. But it is not at all happening. I.e The alert is
not at all firing.
Kindly give some solution.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Thanks and regards
Praveen.AAlert firing is based on the eventlog. Make sure you write to eventlog. Use
either WITH LOG for
RAISERROR or (preferably) design your own messages (sp_addmessage) and speci
fy there to always write
to eventlog.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Praveen" <apveen@.gmail.com> wrote in message
news:1127218958.512008.318710@.g14g2000cwa.googlegroups.com...
>I have configured the alert of
> Type SQL Server event alert
> for the severity 016-Miscellaneous user Error
> DB is all Database
> In response tab, I have confiured the job.
> I have created one procedure in pubs dB like
> create proc test
> as
> begin
> raiserror('going to configure ',16,1)
> end
> After that I have executed this Stored Procedure.
> exec test.
> My understanding is that now the alert should be fired and then it
> should execute a job. But it is not at all happening. I.e The alert is
> not at all firing.
> Kindly give some solution.
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
>
> Thanks and regards
> Praveen.A
>|||Thanks a lot Tibor.
I got a solution. Alert is firing now.

Alert didn't fire

Hi, I create a alert for login failed, the error number
is 18456, although the severity is 14, but when I create
the alert, it is forced by SQL server to write to
application log. Then I go to query analyzer, use a wrong
password for SA, but the alert didn't fire, I go to check
application log, and there is the error message. Would you please tell me
why and how to solve it?
Thanks a lot
Arturo
Message posted via http://www.sqlmonster.com
Did you setup an operator for the alert?
"Arturo Aguilar Landero via SQLMonster.com" <forum@.SQLMonster.com> wrote in
message news:41680c273cbd45c99a3f8d0e735706cf@.SQLMonster.c om...
> Hi, I create a alert for login failed, the error number
> is 18456, although the severity is 14, but when I create
> the alert, it is forced by SQL server to write to
> application log. Then I go to query analyzer, use a wrong
> password for SA, but the alert didn't fire, I go to check
> application log, and there is the error message. Would you please tell me
> why and how to solve it?
> Thanks a lot
> Arturo
> --
> Message posted via http://www.sqlmonster.com
|||yes, already check the operator and works correctly,
with another alert
Message posted via http://www.sqlmonster.com