How to read the SQL log file using TSQL

SQL Server provides a hidden system stored procedure called sp_readerrorlog. This procedure enables you to directly view the SQL Server error log files from a query window and even lets you search for specific keywords within the logs.

The parameters for reading the error log files are as follows:

• Log File Value: 0 = current, 1 = Archive #1, 2 = Archive #2, and so on...
• Log File Type: 1 or NULL = error log, 2 = SQL Agent log
• Search String 1: The first keyword you want to search for.
• Search String 2: An optional second keyword to refine the search results.
• Start Time: The starting point for the search.
• End Time: The end time for the search.
• Sort Order: Use 'N'asc' for ascending order or 'N'desc' for descending order of results.

For example, to search for the word 'backup' in the current error log, you would execute the following SQL query:

EXEC master.dbo.xp_readerrorlog 0, 1, N'backup'

With this approach, you can easily retrieve specific log entries and monitor SQL Server activities in detail.