Effective management of SQL Server logs and different ways to read them

Effective management of SQL Server logs and different ways to read them

Why do we need to manage SQL Server error logs efficiently?
Most important reason is readability, I have faced this situation many times while working with many SQL Server Instances. When we are trouble shooting SQL Server it is very hard go through many lines of logs which are useless, sometimes as DBA we would be working under time pressure.
Few things in SQL Server error log are useless bye default like successful logins (if you have enabled), Successful backups and deadlocks from SQL Server 2008 R2 onwards. Based on my experience it is always good to keep SQL Server logs short and quickly readable from notepad when SQL is down or not accessible. Here are few of the changes we need to implement:

    1. Enable Trace flag 3226, this helps not to log all successful backup completions.
This can enabled by adding -T3226 as start up parameter permanently. This requires down time for  SQL Server however we can avoid this, I will explain the steps in my another article.

    2. Dont enable Deadlock trace flags from SQL Server 2008 R2 onwards, as complete deadlock details are available as part of default System health Extended event. I will explain on How get deadlock details from this extended event in a separate article, I will post the link once it is ready.
  
    3. Last but not least recycle your error logs to keep them small and number of logs or length of the logs can be decided based on Customer requirements.

So far we have reviewed few of the setting those can help keep our SQL Server error log short and sweet, now we will see how to read SQL Server Error log.

SQL Server error log can be read with the help of XP_READERRORLOG or SP_READERRORLOG. My favorite is XP_READERRORLOG as it has more parameters. Details are below:


EXEC master.dbo.xp_readerrorlog 0, 1, null, null, null, null, N'desc'
1st parameter is for error log file number you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2nd Parameter is for which log you want to read: 1 or NULL = error log, 2 = SQL Agent log
3rd Parameter is for 1st string you want to search on.
4th Parameter is for further fine tune your search, which means 4th parameter can be used to filter the output of 3rd or 1st string parameter.
5th Start date time in SQL Server error log.
6th End date time in SQL Server error log.
7th how do you want to sort your output based on date time of error log: N'asc' = ascending, N'desc' = descending.

SP_READERRORLOG error log internally uses XP_READERRORLOG however SP_READERRORLOG has limited number of parameters and they are:

1st parameter is for error log file number you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2nd Parameter is for which log you want to read: 1 or NULL = error log, 2 = SQL Agent log
3rd Parameter is for 1st string you want to search on.
4th Parameter is for further fine tune your search, which means 4th parameter can be used to filter the output of 3rd or 1st string parameter.

Hope this article finds useful. Thanks for reading.