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
- SQL
Server 2000 client installed on the machine where you will run this batch file. - 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.