March 21, 2003 at 1:11 pm
I have sucessfully defined and tested performance alerts. I am now trying to test
setting up sql server event alerts. I shutdown sqlserver and renamed a database's datafile. Restarted sqlserver and did receive messages about this in the event log.
The database was listed as suspect, so my severity level 23 alert should have kick off and emailed me. But it does not. I also have set up a special sql error alerts to catch when a particular log file is missing, etc. These message are also in the event log but do not fire and email me. I know mail is setup correctly because I do get mail on performance alerts. Any help on
this would be appreciated.
Thanks
messages in the event log about the
March 21, 2003 at 1:37 pm
When you mentioned message in event log, Are you referring to machine application/system logs or SQL Server Errorlog?
In order for alert to fire, Message with Severity: 23 has to be in SQL Server Errorlog in this case.
Edited by - Allen_Cui on 03/21/2003 1:38:13 PM
March 21, 2003 at 2:26 pm
I am referring to the application event log.
The only thing in the sql logs is that
it is waiting for recovery. Wonder why
it is not logging a suspect database in
the sql logs?
March 21, 2003 at 2:40 pm
If you simply renamed the database files to make database suspect and restart SQL Server, you should see some kind of error like 'Device activation error or FCB::Open failed' without severity 23 in SQL Server errorlog. I think that is why your alert did not fire.
From BOL.
"Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect
These messages indicate that the integrity of the entire database is in question because of a hardware or software problem.
Severity level 23 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine the extent of the damage. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database."
March 24, 2003 at 8:10 am
Yes, all I did was shut sql down and rename the file. There is not a sev 23 in the sql
log. I did, however, define an alert on
error 17052. There is an event log message:
17052 :
Device activation error. The physical file name 'i:\mssql\data\test\test_data.mdf' may be incorrect.
But all that is in the sql log is:
FCB::Open failed: Could not open device i:\mssql\data\test\test_data.mdf for virtual device number (VDN) 1.
Why doesn't sql log the 17052 error. All I am trying to do is reproduce a "Sql Server
Event Alert". Any ideas on how to test
setting off an alert?
March 24, 2003 at 9:15 am
I don't think the alert can be fired before SQL Server recovery processes complete. You may write a script either to search event log error 17052 or SQL Server errorlog for key word like 'Device activation error', run the script automatically after SQL Server recovery complete and schedule it to run at interval you like.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply