Reading SQL Server Error Log using XP_READERRORLOG

We use sp_readerrorlog for reading the content of the error log of SQL Server and it actually uses xp_readerrorlog extended stored procedure. Since I had to do some troubleshoot with one of servers, I use this for finding some info, and thought share it.

Here are some ways of reading the error log;

In case of you need the code;

USE master;
GO

-- without parameters, load the current log
EXEC xp_readerrorlog;

-- Load the specific error log 
-- 0 is current one and by default there are 6 more old logs
EXEC xp_readerrorlog 0;

-- Second parameter is for log type
-- 1 is for engine and 2 is agent
EXEC xp_readerrorlog 0, 1;

-- Two more additional parameters can be passed 
-- for searching, 1st one is for searching
-- and second one for filter the search - or further searchin within the result
EXEC xp_readerrorlog 0, 1, N'Server', N'Listening';

-- And this can take three more parameters
-- for setting the date range and sorting order
EXEC xp_readerrorlog 0, 1, N'Database', N'resource'
 , '2016-12-21 23:00:18.860', '2016-12-21 23:00:18.930', N'DESC';

How to delete current SQL Server error log? My current log is in GBs.

In a continuous running SQL Server environment, it is not uncommon to see a large error log related to SQL Server that is continuously growing. Recently, a discussion happened on this as this particular environment had a very large error log which had consumed a lot of space in the partition, creating an issue related to space required to data files. Can we delete the error log?

By default, SQL Server maintains 7 error logs; current log and 6 backups of last files created. The current log has no extension and last backups have extensions starting from 1. This setting can be changed by clicking configure of SQL Server Logs folder in Object Explorer - Management if required. Files are located in standard SQL Server path: {partition}:\Program Files\Microsoft SQL Server\{Instance}\MSSQL\Log.

Now if you try to delete the current log, you will get an error because it is being used by SQL Server. Therefore, you need to create a new file as the current log and make the current one as the last backup. The log file cycles with every restart of the SQL Server instance, making the current one as the last backup with extension 1 and creating a new one with no extension. With default setting, all other files starting from 1 to 6, become 2 to 5 and 6th file is deleted. If you cannot restart the service (if it is Production Environment), then this recycling can be manually done by calling sp_cycle_errorlog. This SP closes the current one and creates a new one. Once it is done, you can delete the large one, which is the one with extension 1 now.

EXEC sys.sp_cycle_errorlog;