SQL Server – Error Logs recycle without SQL Server Restart

If some long running error situation , the Error Log can become very big. In those situations , it can be time-consuming to search for specific Messages. 

It is a good idea to recycle the SQL Server Error Logs . To create a new Error Log file without restarting SQL Server , use the sp_cycle_errorlog  system stored procedure.

This closes down the current error log and cycles the error log 

 

--execute  the sp_cycle_errorlog
EXEC sp_cycle_errorlog

 

See Also

SQL Server - Grant execute on all stored procedures

 

Powershell_ErrorLogs

Running the code below within the Powershell interface will list designated Errors Log messages instances (SQL SERVER 2005) listed in the "C:\InstancesPROD_2005.txt" document.
An example of the lists in that document is:

SERVER1\INST1
SERVER2\INST2
etc

Currently the sql code will designated errors failed within the last 2 days. Configure to your requirements.

To run ,  copy and paste straight into a Powershell  cmdlet ,which is a series of commands, usually more than one line, stored in a text file with a .ps1 extension. 

 

----------------------------------CODE START-----------------------------------------

foreach ($svr in get-content "C:\InstancesPROD_2005.txt"){
    $svr
    $dt = new-object "System.Data.DataTable"
    $cn = new-object System.Data.SqlClient.SqlConnection "server=$svr;database=msdb;Integrated Security=sspi"
    $cn.Open()
    $sql = $cn.CreateCommand()
    $sql.CommandText = "

DECLARE @sqlStatement1 VARCHAR(200)
SET @sqlStatement1 = 'master.dbo.xp_readerrorlog'
        CREATE TABLE #Errors (LogDate DATETIME,ProcessInfo NVARCHAR(50),vchMessage varchar(2000))
INSERT #Errors EXEC @sqlStatement1
SELECT LogDate, RTRIM(LTRIM(vchMessage)) FROM #Errors WHERE 
([vchMessage] like '%error%'
   or  [vchMessage] like '%fail%'
   or  [vchMessage] like '%Warning%'
   or  [vchMessage] like '%The SQL Server cannot obtain a LOCK resource at this time%'
   or  [vchMessage] like '%Autogrow of file%in database%cancelled or timed out after%'
   or  [vchMessage] like '%Consider using ALTER DATABASE to set smaller FILEGROWTH%'
   or  [vchMessage] like '% is full%'
   or  [vchMessage] like '% blocking processes%'
   or  [vchMessage] like '%SQL Server has encountered%IO requests taking longer%to complete%'
)
and [vchMessage] not like '%\ERRORLOG%'
and [vchMessage] not like '%Attempting to cycle errorlog%'
and [vchMessage] not like '%Errorlog has been reinitialized.%' 
and [vchMessage] not like '%found 0 errors and repaired 0 errors.%'
and [vchMessage] not like '%without errors%'
and [vchMessage] not like '%This is an informational message%'
and [vchMessage] not like '%WARNING:%Failed to reserve contiguous memory%'
and [vchMessage] not like '%The error log has been reinitialized%'
and [vchMessage] not like '%Setting database option ANSI_WARNINGS%'
and [vchMessage] not like '%Error: 15457, Severity: 0, State: 1%'
and [vchMessage] <>  'Error: 18456, Severity: 14, State: 16.'
AND Logdate > GETDATE() - 2

  
DROP TABLE #Errors
"
    $rdr = $sql.ExecuteReader()
    $dt.Load($rdr)
    $cn.Close()
    $dt | Format-Table -autosize
}

----------------------------------CODE END--------------------------------------------