SQL Server Error Log - Search

The SQL Server error log contains user-defined events and certain system events you can use for troubleshooting. The errors and the informational data will be stored in the error logs. The data will be huge and sometimes it will be difficult to find the information or error from it. 

Below is the query to retrieve the complete log from SSMS.  

EXEC xp_ReadErrorLog

GO

If you want to search through this log, you can use the below syntaxes.

To know the SQL Server version, edition, and other information -

EXEC xp_ReadErrorLog 0, 1, N'Build', N'Edition', NULL, NULL, 'DESC'

GO

To know on which port SQL Server is listening on -

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

GO

To know the CLR version loaded in the SQL Server -

EXEC xp_ReadErrorLog 0, 1, N'CLR version', N'loaded', NULL, NULL, 'DESC'

GO

To know the authentication mode -

EXEC xp_ReadErrorLog 0, 1, N'Authentication Mode', NULL, NULL, NULL, 'DESC'

GO

To know the number of sockets and processors -

EXEC xp_ReadErrorLog 0, 1, N'sockets', N'processors', NULL, NULL, 'DESC'

GO

If you observe the syntax, I've provided two key-words to filter and fetch the required information. The count can be increased.

The same can be applied to find errors.