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.
-
What is wrong with this question?– kakazAug 11, 2019 at 17:06
-
1Define "summary". You can use sp_readerrorlog to input the stuff into a table and then do a SELECT from that table to include only todays events and include that in the email (using a query to sp_send_dbmail). But if you want to refine the info, and only get the non-noise events, then we need to know how you define which events that you want to exclude.– Tibor KarasziAug 11, 2019 at 18:16
-
@kakaz it is not well-defined (and as such, closed as unclear what the OP is asking). Can you write a query that e-mails the exact daily summary the OP wants? I certainly can't. The OP needs to explain exactly what they mean, edit the question to include that much more specific info, and then it can be considered for re-opening. If they just want the full contents of yesterday's error log mailed to someone today, they need to visit tutorials for Database Mail and/or SQL Server Agent. If they have problems accomplishing that, they need to show what they tried and explain their specific problems– Aaron BertrandAug 11, 2019 at 19:23
-
@kakaz I would have closed it for the same reason, but five of your peers reached that consensus first.– Aaron BertrandAug 11, 2019 at 19:26
-
github.com/scsimon/sqlserver/blob/master/usp_error_log– S3SAug 11, 2019 at 21:55
|
Show 1 more comment
1 Answer
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 severity 17
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 [email protected]
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
The @notification_method = 1 means E-mail
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 xp_readerrorlog here
answered Aug 11, 2019 at 14:16
