Checking the status of weekly rebooted SQL Servers

March 2, 2005

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]

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

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_

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

Download check1.bat_

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"

Download checkreboot.bat_