Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 2, 2005

Checking the status of weekly rebooted SQL Servers

By Muthusamy Anantha Kumar aka The MAK

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date