Mining the SQL Server Error Log

I know most people tend to gravitate to the GUI in SSMS to review the SQL Server error log.   This is a perfectly acceptable way to dig into an issue, however, there is another option available.  It's called 'XP_READERRORLOG'.

WHY USE IT?

XP_READERRORLOG is a system extended stored procedure that allows you to explore the error log as a result set.  Why is this stored procedure a useful tool to understand?  First, you should always be prepared for a scenario when you do not have a GUI at your disposal.  Secondly, the stored procedure accepts parameters that limit the results to useful information.  "But you can do this using search in the GUI..."...  that's true, however, using the stored procedure you can query all the servers in your environment at the same time!  So if you are looking to see what servers are experiencing memory pressure, you can mine the logs in the whole environment and get your answer in minutes.  This is better than using the GUI to mine the logs one server at a time.  Another example might be searching for all failed logins during a specific window across your entire environment to identify a potential attack. 

HOW TO USE IT

XP_ERRORLOG used by itself will simply return all the contents of the currently active error log. 

The XP_READERRORLOG procedure accepts 7 parameters.

EXEC XP_READERRORLOG par1,par2,par3,par4,par5,par6,par7 

Par1) Specifies which error log you would like to use.

• 0 =  Current Log
• 1 = Archive #1
• 2 = Archive #2
• etc..

Par2) Specifies the type of log you would like use.  This means not only can you read the error log, but you can also read the SQL agent log.

• 1 or NULL = SQL Error Log
• 2 = SQL Agent Log 

Par3) Search String 1.  This is a string of text you would like to limit your search to.

Par4) Search String 2.  This limits your results even further.

Par5) Eliminate search results older than this value

Par6) Eliminate results more recent than this value

Par7) Order the results

• ASC = results are ordered ascending
• DESC = results are ordered descending 

EXAMPLES

Here are just a handful of examples of things I have searched for over the years.  There is much more I could list, but this should be plenty to get you started.

--performance

-- Search for messages indicating I/O latency

xp_readerrorlog 0,1,N'SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds'

-- Search for messages indicating memory pressure

xp_readerrorlog 0,1,N'memory pressure'

-- Find deadlocks (if trace flag enabling deadlock logging is enabled)

xp_readerrorlog 0,1,N'deadlock enc'

--security

-- Search for failed logins for a specific user from a specific machine 

xp_readerrorlog 0,1,N'Login failed for user ''DomainName\AccountName''',N'xx.xx.x.xxx'

-- Search for failed logins during a specific window and order them descending

xp_readerrorlog 0,1,N'Login failed for user',NULL,'2018-08-13 20:23:19.580','2018-08-13 20:23:59.700', N'DESC'

--VSS backups

-- Find SQL Server databases that are being frozen due to VSS backups not excluding the SQL mount.

xp_readerrorlog 0,1,N'I/O is frozen on database'