SQL Server 2005 & the Windows Event Log

Question

I'm running a SQL Server 2005 Standard. I got several alerts I need to react on. So I created these alerts, but they never appear so that the jobs depending on them are never run.

I found out that for alerts it is necessary that the SQL Server writes to the Windows Event Log, but this is not happening at all. Every source tells me that normally SQL Server is logging automatically, so I've got no clue why my instance does not. Does anyone know an option for (de-)activating the logging to Windows Event Log?

UPDATE:

I see my description is missing some basic information, so I will add this here.

I've got a Mirroring scenario with a Witness. What I'm looking for are the events with IDs 1440 and 1441 which indicate that one server changed it's role vom Principal to Mirror. These should automatically been written to the Windows Event Log, but that does not happen. At all, not only one single message from SQL Server is written to the Event Log.

Answer 1

sp_altermessage to change system messages so they are logged (version/SP dependant)

sp_addmessage for user defined alerts

RAISERROR.. WITH LOG in your code (subject to permission)

Otherwise, what alerts are you looking for?

Answer 2

I finally found the answer. There is a startup parameter for telling the SQL Server that it should not log anything to the Windows Event Log: -n.

To set it you got to open SQL Server Configuration Manager, open the Properties of your SQL Server instance and go to Advanced. There you'll find a field named Startup Parameters. Here just enter (or in my case remove) the parameter -n and your SQL Server will stop writing to the Windows Event Log.