Checking in SQL Server's Heart Beat Article

Mak -

How do I run the VB Script - CheckHeartbeat.vbs in my SQL box?

I just saved it as .vbs then run it but didn't get the script that Status Check was completed.

1. See your task manager for WSCript.exe
(if you see it running, it means it is still running)

2. C:\heartbeat\status.txt should have the status of all the machines listed in C:\heartbeat\ServiceList.txt

Also make sure you do not have any blank spaces in the serverlist.txt

You said add your server names and services.

If my server name is Test then in ServiceList.Txt I add this line:

TEST,MSSQLServer

The service name is MSSQLSERVER.

That should work.

can you ping servername

try commenting the "on error resume next" and try to run.

Here's my code:

'Objective: To check the heartbeat of all SQL Server services
'Date: May 6,2004

on error resume next
Set iFSO = CreateObject("Scripting:FilesyStemObject")
Set oFSO = CreateObject("Scripting:FilesyStemObject")

InputFile="C:\Heartbeat\Servicelist.txt"
OutputFile="C:\Heartbeat\Status.txt"

Set iFile = iFSO.OpenTextFile(inputfile)
Set oFile = oFSO.createTextFile(OutputFile, True)

Do until iFile.AtEndOfLine
servicelist.iFile.Readline
strcomputer=left(servicelist,instr(servicelist,"," )-1)
Service =right(servicelist,len(servicelist)-instr(servicelist,","))
oFile.writeline Now()
oFile.writeline "ServerName:TAKARASQL02 " & strcomputer
oFile.writeline "ServiceName:MSSQLSERVER " & Service

Set objWMIService =nothing
Set objWMIService = GetObject("winmgmts:\\" & strcomputer & "\root\cimv2")

if err.number<>0 then
oFile.writeline "Error: " & err.number
oFile.writeline "Error: " & err.description
else

Set colItems = nothing
query = ""
query = "Select * from Win32_Service where name = '" & Service & "'"
'msgbox query
Set colItems = objWMIService.ExecQuery(query,,48)
if err.number<>0 then
oFile.writeline "Error: " & err.number
oFile.writeline "Error: " & err.description
else
For Each objItem in colItems
oFile.writeline "Status: " & objItem.Status
oFile.writeline "State: " & obj.ItemState
next
end if
end if

Loop

msgbox "SQL Service Status Check completed"

Please copy the code from
http://www.databasejournal.com/featu...le.php/3347241

I compared (FC.exe - File comparison) your code and the code on the article, there are so many differences

One more thing. Please do not hardcode your server names in the VB script. it will end up having two servernames and service names.

I copied the script from the link above and executed it. Ran fine.

Perfect!!! I'm on the next Method. I'll update you later. Thanks a lot.

Please change the emailID to your email id (as mentioned in the article). I already got hundreds of emails from many people when they were trying out some of the articles published by me.

I tried to do a Job Schedule for the script with the email but I got the message that its 4110 in size and larger than available space for Job Step command which was 3328 characters. Then gave this option: Do you want to continue opening this file but have its contents truncated.

Do I say yes?

1. Windows Job Scheduler or SQL jobs or 3rd party?

I use windows Scheduler. Works like a champ

Windows Scheduler

Click Start-settings-control panel-"scheduled tasks"

and you cannot say "YES" if something is going to get truncated.

Is it possible to get an email even though the servers are okay because right now I only get an email if a server failed.

Just to make sure that the script is running.

For testing, just stop SQLServerAgent service in one of your development box and run this script.

If you tweak the script to send emails even it the servers are OK then you will end up having one email for every service you mentioned in the servicelist.txt

you can send c:\heartbeat\status.txt as attachment.

Got it, Thanks.

I more thing. I have a database for an ERP system. Sometimes a user gets kicked but when they try to login again the system tells them they're still login.

A table called Activity doesn't take out the user when they're kicked. Do you have a VB Script that can check the session between SQL and Windows.