Did you check the ERRORLOG

Are you checking your SQL Server ERRORLOGS at least daily? If not you should. If you have 10 gazillion servers then I would suggest creating some kind of central script or repository so you can easily check them.  The other day I stumbled across the following error in one of our servers:

Error: 602, Severity: 21, State: 30.

Come to find out this error caused an application outage. Of course people were scratching their heads looking for answers when a quick inspection of the ERRORLOG would have clued them in on what happened to the server. This incident reminded me that I need to be more diligent about checking the ERRORLOGs on our critical servers throughout the day.

I wrote a handy script that I can execute using my Executor Keyboard Launcher. The script takes in a parameter and then will run a series of WMI queries against the server you pass. It is a combo between WMI calls and calls to a central database that stores some other information about the Server.  The script will also display the current ERRORLOG on the screen. The data is dumped onto a single HTML file report that I open up for a quick snapshot of the server.

The Data

  1. Ping the Server (WMI)
  2. Location of Server and IP Address (WMI/DB Query)
  3. Apps on the server (DB Query)
  4. Type Physical or Virtual (DB Query)
  5. Operating System version and build (WMI)
  6. Hard drives TotalSize/FreeSpace/%Free (WMI)
  7. Memory: Total/Free/%Free (WMI)
  8. Processor ProcNumber/Type/MaxClockSpeed (WMI)
  9. Processor Utilization %Util/Total Util (WMI)
  10. SQL Info Version/Port Number (DB Query)
  11. SQL Server Logs (VB Script Parse of physical File)

This is just the On Demand script. I also have a script that runs each morning that I check first thing when I get in. This is where I discovered the Error mentioned above.

Here are some of the Subs used in the VBScript to do the WMI calls

Disk Space

Sub DiskSpace
On Error Resume next
Set colItems = objWMIService.ExecQuery (“Select * from Win32_LogicalDisk where MediaType = 12”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if

htmlfile.Writeline (“<table style=’font-size: 10pt’ border=1><tr style=’background-color: #DDDDDD; font-weight: bold;’><td>Drive</td><td>%Free</td><td>TotalSize(MB)</td><td>FreeSpace(MB)</td></tr>”)
For Each objItem in colItems
freeSpace = Round((objItem.FreeSpace/objItem.Size) * 100, 2)
if freeSpace < 10.00 then
freeSpaceDS = “<font color=’red’>” & freeSpace & “</font>”
elseif freespace > 10.00 and freespace < 30 then
freeSpaceDS = “<font color=’orange’>” & freeSpace & “</font>”
else
freeSpaceDS = “<font color=’green’>” & freeSpace & “</font>”
end if

htmlfile.writeLine(“<tr><td>” & objItem.Name & “</td><td>” & freespaceDS & “</td><td>” & Round(((objItem.Size/1024)/1024),2) & “</td><td>” & Round(((objItem.FreeSpace/1024)/1024),2) & “</td></tr>”)
Next
htmlfile.writeLine(“</table”)
End Sub

On the Disk Space and Memory I added some color coding. If a certain threshold then write the data out in Red. This should help notify of any problem.

Processor

Sub Processor
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_Processor”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if
htmlfile.WriteLine(“<table style=’font-size: 10pt’ border=1><tr style=’background-color: #DDDDDD; font-weight: bold;’><td>Processor</td><td>Name</td><td>MaxClockSpeed</td></tr>”)
For Each objItem5 in colItems5
htmlfile.WriteLine(“<tr><td>” & objItem5.DeviceID & “</td><td>” & objItem5.Name & “</td><td>” & objItem5.MaxClockSpeed & “</td></tr>”)
Next
htmlfile.WriteLine(“</table><br>”)
End Sub

Operating System

Sub OS
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_OperatingSystem”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if
For Each objItem5 in colItems5
htmlfile.WriteLine(“OS: ” & objItem5.Caption & ” — ” & objItem5.CSDVersion & ” — ” & objItem5.BuildNumber & “<br>”)
Next
End Sub

Hardware

Sub Hardware
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_ComputerSystem”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if

For Each objItem5 in colItems5
htmlfile.WriteLine(“Hardware: ” & objItem5.Manufacturer & ” — ” & objItem5.Model & “<br>”)
Next
End Sub

Returns the Make and Model of the hardware of the server.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s