How to Read the SQL Server log files using T-SQL

The system stored procedure sp_readerrorlog allows to read the contents of the SQL Server error log files directly from a query window and also allows to search for certain keywords when reading directly from the error file.
Lets check following sample: -

CREATE PROC [sys].[sp_readerrorlog](
@p1 INT = 0,
@p2 INT = NULL,
@p3 VARCHAR(255) = NULL,
@p4 VARCHAR(255) = NULL)
AS
BEGIN

IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1)
BEGIN
RAISERROR(15003,-1,-1, N'securityadmin')
RETURN (1)
END

IF (@p2 IS NULL)
EXEC sys.xp_readerrorlog @p1
ELSE
EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4
END

This procedure takes four parameters:
Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results
If you do not pass any parameters this will return the contents of the current error log.

Some examples:

• EXEC sp_readerrorlog 6 -returns all of the rows from the 6th archived error log
• EXEC sp_readerrorlog 6, 1, '2005' - returns just 8 rows wherever the value 2005 appears
• EXEC sp_readerrorlog 6, 1, '2005', 'exec' - returns only rows where the value '2005' and 'exec' exist