I want to generate a daily summary of SQL Server Event Logs which will be sent via email. I have currently been able to set up SQL email alerts for error logs,but they are sent individualy when an error occur. What i want to achieve it a daily summary report of all SQL Server logs which will be sent directly to an email. This way without going to sql server logs on SSMS, i will see all server logs recorded for a particular day.
If you are looking to track specific severity errors or specific events, you could use alerts
Events are generated by SQL Server and entered into the Microsoft Windows application log. SQL Server Agent reads the application log and compares events written there to alerts that you have defined. When SQL Server Agent finds a match, it fires an alert, which is an automated response to an event. In addition to monitoring SQL Server events, SQL Server Agent can also monitor performance conditions and Windows Management Instrumentation (WMI) events.
You can read up on configuring all of this here
The steps in a nutshell
- Configure sql server database mail
- Add alerts for the events you want to track
An example of adding an alert for
USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'Error 17 Alert', @message_id=0, @severity=17, @enabled=1, @delay_between_responses=0, @include_event_description_in=1; GO
- Configure the operator
An example of creating an operator
DBA. Mails will be sent to
USE msdb; GO EXEC msdb.dbo.sp_add_operator @name = 'DBA', @enabled = 1, @email_address = '[email protected]'; GO
- Configure the alert to be mapped to the operator
@notification_method = 1 means
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error 17 Alert', @operator_name = 'DBAs', @notification_method = 1; GO
Repeat the second and fourth step for additional alerts.
If you want to create your own reports, you could cook something up with the
sys.xp_readerrorlog function and add your own filtering. What you want to keep or discard depends on your requirements.
More on reading the error log with