The Multifunctioning DBA

Oct 28 2008   5:59PM GMT

MS SQL Server Monitoring Part 3

Colin Smith Colin Smith Profile: Colin Smith

Tags:
Exchange Server ActiveSync

In MS SQL Server Monitoring Part 2 we talked about inserting the Marker into the ERRORLOG file. Now that we know we can do that we need to look at how we can scan the ERRORLOG file for errors, and only new errors that we care about. I do not know about you but I do not want to get a notification every time I get a logon failure. I only want to know about important events such as backups failing, severe errors, and mirroring failures. You can change things so you are notified about anything you would like though. This script was put together in such a way that it is easily customizable for anything you would like.

Here is the code for the Get_Errors function of the script. I am going to highlight some things but this will not get very indepth at this time. I would be happy to give a more indepth look into the code if you like though. Just let me know here or at my site sysadminsmith.com.

###########################################

function Get_Errors

###########################################

{

#Echo “Get Errors”

$ignore = get-content “h:\$folder\ignore_strings”

$estrings = Get-Content “h:\$folder\Error_Strings”

$i = 0

$n = 0

$descript = 0

$mark = 0

foreach ($line in $log)

{

if ($line -like “*Monitor Marker*”)

{

$i ++

}

}

foreach ($line in $log)

{

if ($descript -eq 1)

{

Echo $line >> “H:\$folder\monitor.out”

$descript = 0

}

if ($line -like “*Monitor Marker*”)

{

$n ++

}

#if (($n -ge $i) -and ($line -like “*Error*”) -and ($line -notlike “*logon*”))

if ($n -ge $i)

{

$ignoreit = 0

foreach ($estring in $estrings)

{

if ($line -like “*$estring*”)

{

$ignoreit = 0

foreach ($istring in $ignore)

{

if ($line -like “*$istring*”)

{

$ignoreit = 1

}

}

break

}

else

{

$ignoreit = 1

}

}

if ($ignoreit -eq 0)

{

$mark = 1

$descript = 1

echo $folder >> “H:\Alert\Alert”

if (!(test-path “H:\$folder\monitor.out”))

{

New-Item -type File “H:\$folder\monitor.out”

}

Echo $line >> “H:\$folder\monitor.out”

}

}

}

if ($mark -gt 0)

{

First I get the Ignore_strings and the error_strings. The error_strings is what I use to determine if the line I am looking at in the ERRORLOG file is important enough to evaluate further. If no Error_string is found on that line then I just move on. The Ignore_strings are words or phrases that would make a line not important to me even if I did find a word or phrase that is in Error_strings. This is how I can control what errors are important to me and what ones are not. Then a couple more variables for flow control. Then I can get into the meat of the function. I start in and look at the ERRORLOG. This time I am only looking for the Monitor Marker that I have put into the ERRORLOG last time I found an error. So I count everytime I see Monitor Marker and put that into a variable. Once I have looked through the entire file and have a good count of the Monitor Marker entries I then start again. This time I again count Monitor Marker until this count is equal to the previous count. Once it is equal then I know that every line from here on out I need to look for anything that is my error_strings file. If I find any lines that have any of my error words in them I then also look for any of the ignore words in that line. If I do not find any ignore words then I output the error line to a file and I also get the next line in ERRORLOG since that is really where the important data is held. I do that for every line left in the ERRORLOG file. This way if more than one error has occurred I will be notified of them all. If I have found any errors I then input a Monitor Marker into the ERRORLOG by doing what I talked about in MS SQL Server Monitoring Part 2.

This pretty much covers how I am looking for and extracting the Errors out of the ERRORLOG. Next time we will discuss how to notify someone about the errors. I hope that this has been helpful and informative. If you have any questions or comments please leave them here or head to sysadminsmith.com and leave me an email, I will be happy to discuss this further with you.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: