Showing posts with label troubleshoot sql server. Show all posts
Showing posts with label troubleshoot sql server. Show all posts

Monday, September 7, 2020

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.


Big Data & SQL

Hi Everybody, Please do visit my new blog that has much more information about Big Data and SQL. The site covers big data and almost all the...