Error Log Management in SQL Server

There are few system stored procs which helps us to manage the error logs in SQL Server

1. msdb.dbo.sp_cycle_errorlog
2. msdb.dbo.sp_cycle_agent_errorlog
3. xp_readerrorlog

xp_readerrorlog  To view current error log, This SP takes 4 parameters
1. 0 – Current Log file, 1 (Archive#1), 2(Archive#2)
2. 1, 2 (1 Indicates SQL Server, 2 Indicates SQL Agent)
3. Search Text
4. Refine Search with in the search
5. StartDate
6. EndDate
7. Asc | Desc

Examples
xp_readerrorlog 0            -- Reads SQL Server Error Log
xp_readerrorlog -1,2        -- Reads SQL Server Agent Log
xp_readerrorlog 0,1, "Logging SQL Server messages in file"  This command helps to identify the location of error log file.

This above system stored procedure helps us to create a new error log file without restarting the SQL Service & SQL Agent Service

This will creates a new error log file from the point when you execute it. It requires sysadmin role to execute.