To prevent databases from serious problems , DBAs usually setup alerts for some particular errors, for example the error " table corrupt" could lead to "database corrupted".  But there are so many alerts for only this "table corrupt" error message(about 40). If you don't want to set all these 40 alerts on your server, neither want to miss any of them, you can simply set this only alert job instead.

In addition, You can combine as many specific error messages as you want to monitor in just one single alert.  For example you can combine  " table corrupt" and " DBCC" to monitor both of them by using this single job.

It applies to SQL Server 6.5/7.0/2000 

HOW THE JOB WORKS 

Basically, it will look up the SQL Server Error log files for specific error strings that the DBA wants to be notified whenever occurring (for example : " table corrupt" and "DBCC") and then send email the error log lines that comprise the strings (if exists ) to the DBA. 


HOW TO CREATE THE JOB:


 1. Create a text file comprise the strings (For example "Table corrupt"). Name it search.txt

Note: for more  information on how to create this file please refer to documentation for Window NT Findstr Utility or Microsoft SQl Server Administrator's companion book.

 2. Create a T-SQL job using this script: 
 exec xp_sendmail ‘nguyen, stephanie’
    @subject = ' Search Errorlog'
    @query =   "declare @result varchar(125) 
  exec @result = xp_cmdshell ' findstr /i /g:c:\mssql7\log\search.txt      \mssql7\log\errorlog*.* ' " 

Arguments: 

 - c:\mssql7\log\search.txt  is the path of search.txt file 
 - \mssql7\log\errorlog*.*  is the path of  the MS SQL Server error log file
   Note: make sure that SQL Mail and SQL Server Agent are running; and a valid MAPI Profile

 3. Schedule to run this job as a recurring job.