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