The Multifunctioning DBA

Oct 23 2008   9:58PM GMT

MS SQL Server Monitoring Part 2

Colin Smith Colin Smith Profile: Colin Smith

Tags:
Exchange Server ActiveSync

OK so in MS SQL Server Monitoring we left off asking about how we can insert a Marker into the ERRORLOG file while the SQL Server is running and the SQL Server has the file locked. Actually, the answer is pretty simple it is the how that is more problematic. So the solution is to have the SQL Server input the line that we want into the ERRORLOG for us. This can be done with a SQL query as follows:

raiserror (‘Monitor Marker’, 1, 2) With Log

That is simple enough but now we need to figure out how to get Powershell to do that for us. In order to do this I did the following:

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

#####    Enter Monitor Marker    ##########

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

        $monitorfile

        Echo “Error log $errorlog”

        Echo “Echo Monitor Marker”

        if ($errorlog -ne $monitorfile)

{

            Echo “Monitor Marker” >> $monitorfile

        }

        else

{

            $sqlquery = “raiserror (‘Monitor Marker’, 1, 2) With Log”

            $SqlCatalog = “master”

            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

            $SqlConnection.ConnectionString = “Server = $SqlServerString; Database = $SqlCatalog; Integrated Security = True”

            $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

            $SqlCmd.CommandText = $sqlquery

            $SqlConnection.Open()

            $SqlCmd.Connection = $SqlConnection

            $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

            $SqlAdapter.SelectCommand = $SqlCmd

            $DataSet = New-Object System.Data.DataSet

            $SqlConnection.Close()

            $SqlAdapter.Fill($DataSet)

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

        }

    }

That is the function within my monitoring script that enters the Marker into the ERRORLOG for me. This only runs and enters the Marker if I have found an error since the previous time that monitoring ran. I run the monitoring script every ten minutes so that I know almost as soon as an error occurs.

Now that I know I can get a Marker of some sort into the ERRORLOG now I need to make sure that I can parse the ERRORLOG file and look for only meaningful errors and only ones that have not been dealt with already. This gets a bit tricky since I could not find a way to read the file from the bottom up to the last Monitor Marker. This is really the only part of the script that I think is a bit sloppy and if anyone knows a better way to do this then please let me know. In order to get only the new errors that I am interested in I have to parse the file two times every time the script runs. Once to count the number of Markers in the file and the other to find errors after the last Marker. I know this is not the cleanest way but it is the best I can do at this time. It still runs very quickly and does not have a problem finishing in well under one minute. We will again pick up here next time and we will go over that section of code. Please let me know if you have any questions about this or ideas on how to fix my double parsing issue. You can also check out my site at sysadminsmith.com and drop me an email if you like.

See MS SQL Server Monitoring Part 3

 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: