Results 1 to 15 of 15

Thread: Checking in SQL Server's Heart Beat Article

  1. #1
    Join Date
    Dec 2003
    Posts
    181

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Also make sure you do not have any blank spaces in the serverlist.txt

  4. #4
    Join Date
    Dec 2003
    Posts
    181
    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.

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    That should work.

    can you ping servername

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

  6. #6
    Join Date
    Dec 2003
    Posts
    181
    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"

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  9. #9
    Join Date
    Dec 2003
    Posts
    181
    Perfect!!! I'm on the next Method. I'll update you later. Thanks a lot.

  10. #10
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  11. #11
    Join Date
    Dec 2003
    Posts
    181
    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?

  12. #12
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.
    Last edited by MAK; 05-07-2004 at 09:02 AM.

  13. #13
    Join Date
    Dec 2003
    Posts
    181
    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.

  14. #14
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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.

  15. #15
    Join Date
    Dec 2003
    Posts
    181
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •