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 
read from error log


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.

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s