25 November,2012 by Jack Vamvas
DBCC ERRORLOG and master..sp_cycle_errorlog recycle the SQL Server Error Log Files without a SQL Server restart required. Both the commands have the same impact, so either one will complete the goal of cycling through the error logs..
When calling master..sp_cycle_errorlog , the code within the stored procedure requests an execute of DBCC ERRORLOG . There are some extra built in checks , such as checking if the requestor is a sysadmin role member and if not raising an error.
if (not (is_srvrolemember('sysadmin') = 1 begin raiserror(15247,-1,-1) return(1) end
Depending on configurations SQL Server Error log files can grow. Consider scheduling a regular job to recycle the logs
By default, only the previous six log files are kept . The text version of the log files are found in \Program Files\Microsoft SQL Server\MSSQL\LOG\*. Also use , SQL Server xp_regread with T-SQL for SQL Error Log Path and ... to find the path
An added option , if you’re truncating log files is to first backup the log files.