SQL Server: How to Collect Events and Errors

  • Article

Introduction

This article discusses the ways to get latest triggered messages (errors and warnings) to facilitate SQL Server incident monitoring.  Thus helping detect and trouble shoot issue(s) related to a process or scheduled task.

Using "sp_readerrorlog"

This is a system stored procedure undocumented in Books Online (BOL), which is very useful in several situations.

It allows you to read log files of a SQL Server instance and search certain "keywords" for one or more specific error messages. Really, this is a "more intuitive" stored procedure, based on sys.xp_readerrorlog to facilitate queryings in log files.

It's possible run stored procedure "sp_readerrorlog" without setting any parameters, in this case the output data is related to Current log file (same results when first parameter is "0" - default value).

There are 7 "optional parameters" that can be used for this stored procedure, see sequence below:

  1. Log File: 0 = Current (default), 1 = File "#1", 2 = File "#2", and so forth;
  2. Message Type: 1 or NULL = Log of SQL Server instance (default), 2 = Log of SQL Agent;
  3. Principal String Search: Word or text to search results;
  4. Secondary String Search: Word or text to search results secondarily on "Principal String Search";
  5. Date / Time Start: search for messages stamped from a start date and time;
  6. Date / Time Final: search for messages stamped until a final date and time;
  7. Order: 'ASC' = Ascending, 'DESC' = Descending

The work to get logged messages is performed by "xp_readerrorlog", however this system stored procedure only first four input parameters avaliable, as was indicated above.

Using SSMS

Using SSMS(SQL Server Management Studio) we can easily get occurrences and informations about your SQL Server instance and SQL Agent.

These two Log messages are separated and can be obtained in different folders to management.

Querying Events on SQL Server Instance

You can access all occurrences available on SQL Server Instance, accessing "Management" folder -> "SQL Server Logs".

See the image below:

You can view each log file separated, simply double click to open SQL Server Log file and then you can obtain details of each occurrence (Date, Log File, Source and Message).

See the image below:

Querying Events on SQL Server Agent

You can access all occurrences available on SQL Server Agent, accessing "Error Logs" folder -> "SQL Server Logs".

See the image below:

  

References

See Also

Other Languages