The Multifunctioning DBA

Oct 20 2008   11:50PM GMT

MS SQL Server Monitoring

Colin Smith Colin Smith Profile: Colin Smith

Tags:
Exchange Server ActiveSync

One of the first tasks that was assigned to me after moving to the DBA team was to find and implement a monitoring solution for MS SQL Server instances. We wanted to do this in house, if possible, in order to save money. In order to do this I decided to try and use Windows Powershell. Before I could start coding I had to determine exactly what I wanted to be able to accomplish with this monitoring. I took some time and worked with the other members in my team and came up with the following.

  1. Dynamically determine what day and time it is and who is on call for that time. Send notifications of problems to the correct people based on these variables.
  2. Determine what errors are important to us and what notifications are not important to us.
  3. Continue sending out notifications every ten minutes until someone responds to the error.
  4. Have a way to respond to issues.

That is the very basics. At first we just wanted to be able to get errors and respond essentially. I figured that I would need at least two scripts to accommodate this. I will need one script that will monitor the ERRORLOG file and one that will allow us to respond to the error and let the script know that we have responded. This all sounds simple enough but I soon found out that it is not so easy.

My team has a listing of every SQL Server instance in the company, but since I am the new guy I wanted to verify that I was not missing any. I found a program called SQL Recon that scans the network and reports back on all SQL Server instances on the network. I ran this utility, sifted through the results and found that most but not all of the instances were documented. I documented the missing servers and then started to figure out how to monitor them all.

I know that I want to monitor the ERRORLOG file that SQL Server writes to for any errors that occur. Using Powershell it is easy enough to read in a file and scan each line for keywords or phrases. IE..

$sqlerrors = Get-Content “C:\ERRORLOG”

Echo “############ Contents of the Errorlog File ##################”

$sqlerrors

Echo “############ Contents of the Errorlog File ##################`n”

foreach ($sqlerror in $sqlerrors)

{

        if ($sqlerror -like “*error*”)

        {

            echo “`nI found the word ERROR in this line:”

            $sqlerror

        }

}

This code will get you the following Output.


PS C:\Documents and Settings\smithc> . ‘C:\DOCUME~1\smithc\LOCALS~1\Temp\Untitled4.ps1′
############   Contents of the Errorlog File   ##################
Sample log file
Error on this line
nothing on this line
Sev on this line
Error on this line that is due to a logon
############   Contents of the Errorlog File   ##################

I found the word ERROR in this line:
Error on this line

I found the word ERROR in this line:
Error on this line that is due to a logon
PS C:\Documents and Settings\smithc>

So as you can see reading the log and extracting just the lines that I want is fairly straightforward, the first big problem comes up with finding a way to only find errors that I have not already dealt with. I need to find a way to tell the script where to start caring about the errors in the file. I figured that it would be easy to put a Marker in the ERRORLOG file and the script could use that marker as a way to know what errors are old and what errors are new. No I ask a question to you. How do you edit the ERRORLOG file since the SQL Server Agent always has it locked? That is the first big hurdle and we will deal with this next time.

Thanks for checking out my first Blog entry here and I hope that you are enjoying it and I hope that this will be useful to someone out there. This script could be modified to monitor any application that has an out file. If you have any questions about this please leave some comments or head over to my website at sysadminsmith.com. I will do anything I can to help you out and I also do not mind learning from you. 

Check out part 2 of this series at MS SQL Server Monitoring Part 2

 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: