Reading/scanning SQL Server Logs is a required DBA work item in all my work environments. I know there are lots different ways, mostly are using undocumented xp_readerrorlog or sp_readerrorlog. Even in PS way, this xp_readerrorlog is wrapped as a sql command in the PS script. Actually, with PS and Regular Expression, we can easily find a better and more powerful way to scan error log.
Case Scenario: (Scan SQL Server Error Log)
We need to scan sql server error logs of about 150 SQL instances every hour to check for the past hour, whether there are any errors. We are only interested in log entries that contain specific key words, like “failed”, “error”, “cannot” etc.
(assumption: we only need to scan the current sql error log)
PowerShell Solution:
#Executed on Win7/PS3/SQL2012 against SQL Server 2005+ instances
add-type -AssemblyName "Microsoft.SqlServer.SqlWmiManagement, version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
[string] $filter = '(?<!no|without|not|0)\s+(fail|error|cannot)';
$s = new-object "Microsoft.SqlServer.Management.smo.Server" "tp_w520";#tp_w520 is a my test sql instance, replace it with your own.
$dt=$s.ReadErrorLog();
$dt | ? {$_.text -match $filter -and $_.logdate -ge (get-date).AddHours(-1)} | select logdate, processinfo, text | ft -auto –Wrap
A quick explanation: there are some log entries which indeed have some key words we are looking for, but we are still not interested in these entries, see the following:
“DBCC CHECKDB (AdventureWorks2012) executed by TP_W520\jy found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds”
I have seen some other entries, like “… no error found”, “…without error” etc, and these info should be filtered out as well.
That’s how I configure the $filter, whose meaning is as follows:
(note: I use Expresso 3.0 http://www.ultrapico.com/Expresso.htm).
A sample result is like:
Advantage Analysis:
1. ReadErrorLog() method is a documented method in MSDN, and it is there since sql server 2005, while xp_readerrorlog is not documented and can change anytime.
2. Regular Expression is very powerful, and I cannot over-exaggerate its flexibility and powerfulness. If we want to monitor some complex application error message, using this approach will be a great help.
Other Thoughts:
With this PS method, we can easily dump the result into a central monitoring table and set up different business rules based on the sql instance (which supports different applications), then send the information to the corresponding application support team. For some entries (like failed login, space used up), the log entry will be sent to DBA team to check.
