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.
- 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.
Create a folder C:\CheckReboot. [Refer Fig 1.0]
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
Note: Please change all of the SQL Server names that are available in your environment.
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
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
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.
REM Type: Batch File REM Created by: MAK REM Contact: email@example.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 "firstname.lastname@example.org" "c:\checkreboot\ErrorStatus.log" "Reboot status Failures" c:\CheckReboot\sendsmtp.vbs "email@example.com" "c:\checkreboot\SuccessReboot.log" "Reboot status Success"
Note: Please change the email id in the batch file checkreboot.bat to your email address.