-2

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.

closed as unclear what you're asking by Erik Darling, mustaccio, Mr.Brownstone, MDCCL, Max Vernon Aug 11 at 15:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What is wrong with this question? – kakaz Aug 11 at 17:06
  • 1
    Define "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 Karaszi Aug 11 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 Bertrand Aug 11 at 19:23
  • @kakaz I would have closed it for the same reason, but five of your peers reached that consensus first. – Aaron Bertrand Aug 11 at 19:26
1

If you are looking to track specific severity errors or specific events, you could use alerts

Microsoft Docs on 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 DBA@mycompany.com

USE msdb;
GO
EXEC msdb.dbo.sp_add_operator
  @name = 'DBA',
  @enabled = 1,
  @email_address = 'DBAs@mycompany.com';
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

Not the answer you're looking for? Browse other questions tagged or ask your own question.