Powershell and SQL Error Logs

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--------------------------------------------

 For an alternative method to read sql server error logs and powershell Read SQL Server Error Logs with Powershell and SMO

Related Posts  To Powershell and Error Logs

Powershell Scripts for DBA

Powershell List all Patches Updates on a Server

SQL Server – Error Logs recycle without SQL Server Restart