Checking the status of weekly rebooted SQL Servers

Wednesday Mar 2nd 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn how to take advantage of VB-scripting, the OSQL command line utility and an MS-Dos batch file to consolidate and check the status of all of the rebooted SQL Servers on the network.

SQL Server database administrators are often faced with hundreds of emails and pages when all of the SQL Servers are scheduled for rebooting. In some organizations, the reboot cycles are performed on a weekly basis and in some organizations, the reboot cycles are performed on a monthly basis. If there are few SQL Servers on the network, it is not that tedious to check a few emails and pages from the network monitoring agents. Monitoring agents usually send emails when the system starts rebooting and when the system is coming back.

Sometimes the Operating system will be up and running but the SQL Server service will not be. Sometimes even the operating system will not come up.

If there are hundreds of servers, it is understandable when the database administrators ignore the flood of emails and pages.

In this article, I will discuss how to take advantage of VB-scripting, the OSQL command line utility and an MS-Dos batch file to consolidate and check the status of all of the rebooted SQL Servers on the network. Instead of checking hundreds of pages and emails, the database administrator will get two emails or pages, which will have all the necessary status information of all of the SQL servers on the network.

Let us assume that all of the SQL Server boxes are being rebooted on a weekly basis on Sunday at 1 am.

Pre-requisite

  1. SQL Server 2000 client installed on the machine where you will run this batch file.
  2. The windows login used to run this should have access to all of the SQL Servers listed in serverlist.txt as described in Step 2.

Step 1

Create a folder C:\CheckReboot. [Refer Fig 1.0]


Fig 1.0

Step 2

Create c:\CheckReboot\Serverlist.txt and list all of the SQL Server names and instance names as shown below. [Refer Fig 1.1]

SQL
Claire
Secondary
Secondary\instance1
SQLClusterA
Ebony


Fig 1.1

Note: Please change all of the SQL Server names that are available in your environment.

Step 3

Create c:\CheckReboot\Query.sql and copy and paste the code below into it. [Refer Fig 1.2]

set nocount on
declare @x varchar(70)
set @x=ltrim(rtrim(@@servername))+':'+left(@@version,50)
print @x

Download query.sql_


Fig 1.2

Step 4

Create c:\CheckReboot\Check1.bat and copy and paste the code below into it. [Refer Fig 1.3]

REM Runs OSQL command line utility for any server name that has been 
REM passed as parameter
REM Created by MAK
REM Date: 1/25/2005
REM please change the path of OSQL.exe if it is not same like shown below
REM Please use -Uloginame -Ppassword if you like to use SQL authentication
"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\OSQL.exe" 
  -S%1 -t0 -n -E -ic:\CheckReboot\query.sql -oc:\CheckReboot\output.txt
if errorlevel 1 goto end
Echo %1 >> c:\CheckReboot\SuccessReboot.log
type c:\CheckReboot\output.txt >> c:\CheckReboot\SuccessReboot.log
Goto Finalend

:end
Echo %1 >> c:\CheckReboot\ErrorStatus.log
type c:\CheckReboot\output.txt >> c:\CheckReboot\ErrorStatus.log

:Finalend


Fig 1.3

Download check1.bat_

Note:

a. This batch file calls OSQL.exe using windows authentication. If you want to use SQL Server authentication, please remove "-E" from the parameter list and add "-Uloginname -Ppassword."

b. In your environment, if the path of OSQL.exe is not same as in the batch file, please identify the location of OSQL.exe and update the path information.

Step 5

REM Type: Batch File
REM Created by: MAK
REM Contact: mak_999@yahoo.com
REM Execute Check1.bat for every servers listed in Serverlist.txt
Echo Servers error status > c:\CheckReboot\ErrorStatus.log
Echo Checking servers that are re-booted - Started
date/t > c:\CheckReboot\SuccessReboot.log
time/t >> c:\CheckReboot\SuccessReboot.log
for /f "tokens=1,2,3 delims=," %%i in (C:\CheckReboot\Serverlist.txt)   
   do CALL "C:\CheckReboot\check1.bat" %%i
c:\CheckReboot\sendsmtp.vbs "mak_999@yahoo.com" 
   "c:\checkreboot\ErrorStatus.log" "Reboot status Failures"
c:\CheckReboot\sendsmtp.vbs "mak_999@yahoo.com" 
   "c:\checkreboot\SuccessReboot.log" "Reboot status Success"


Fig 1.4

Download checkreboot.bat_

Note: Please change the email id in the batch file checkreboot.bat to your email address.



Step 6


Create c:\checkreboot\sendsmtp.vbs and copy and paste the code below into it. [Refer Fig 1.5]



'Send SMTP Email
'Created by : MAK
'Contact: mak_999@yahoo.com

VEmail=WScript.Arguments(0)
VFilename=WScript.Arguments(1)
VSubject=WScript.Arguments(2)

Const ForReading = 1 
Const ForWriting = 2 

Set fso = CreateObject
 ("Scripting.FileSystemObject") 

Set ReadFile = fso.OpenTextFile
 (VFilename, ForReading, TristateFalse) 
thisTXT = ReadFile.ReadAll 

Set objMessage = 
 CreateObject("CDO.Message") 
objMessage.Subject = VSubject
objMessage.Sender = VEmail
objMessage.To = VEmail
objMessage.TextBody = thisTXT
'msgbox y
'The line below shows how to send 
'a webpage from a remote site 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/
 configuration/sendusing") = 2 
'Name or IP of Remote SMTP Server 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/
 configuration/smtpserver") = 
  "optonline.net" 
'Server port (typically 25) 
objMessage.Configuration.Fields.Item _ 
("http://schemas.microsoft.com/cdo/
 configuration/smtpserverport") = 25 
objMessage.Configuration.Fields.Update 

objMessage.Send


Fig 1.5

Download sendsmtp.vbs_

Note: Please change the SMTP server name to your SMTP server name.

Step 7

Execute the CheckReboot.bat file as shown below. [Refer Fig 1.6]


Fig 1.6

Once you execute this batch file, it checks the status of all of the listed servers and sends two emails. One email will have all of the servers that were not successfully rebooted along with corresponding error messages [Refer Fig 1.7] and another email will have a list of the servers that were successfully rebooted with the proper server name and version. [Refer Fig 1.8]


Fig 1.7


Fig 1.8

If the reboot cycle is around 1AM, schedule this "checkreboot.bat" to be scheduled for 2AM.

Conclusion

This article examined how to get the status of all the rebooted servers on the network.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved