Testing whether a Windows Service is shut down as part of a scheduled SQL job

Posted on 2008-11-06
Medium Priority
Last Modified: 2012-06-21
We have a nightly Maintenance job that runs on one of our servers that:
A) Notify's our Analysts to get out of the DB
B) Shuts down windows services via a called batch file.
C) Drops and re-creates a couple tables with ~ 9 million rows
D) Indexes the final table
E) Starts the services that were stopped in B
F) Notify's Analysts all done

If any of the steps fails, it sends out a failure notification. The problem we've encountered several times is step B always succeeds in CALLING the script and running it, but on rare occasions the services don't actually shut down. This is known as a Bad Thing, in it introduces duplicate records into the DB, which blows up the indexes and takes a lot of time to fix.
What we need is a suggestion on how to , between B & C above, test whether the services are on or off and shut down the SQL job if they are not. We have a script for testing and notifying us when it is not, but it always comes back as a success as far as SQL is concerned. Either it finds they are shut off and reports "success" (this is good), or it finds they are turned on, sends out the warnings to our pagers and then reports to SQL "success" (this is bad).
Question by:efd_support

Accepted Solution

pjcoole earned 2000 total points
ID: 22898491
If you are using at Bat file to stop and start the services why not check the ERROR level after running the command.  The following are the return codes for the NET Start and NET stop commands.

 - 0 = Success
 - 1 = Not Supported
 - 2 = Access Denied
 - 3 = Dependent Services Running
 - 4 = Invalid Service Control
 - 5 = Service Cannot Accept Control
 - 6 = Service Not Active
 - 7 = Service Request Timeout
 - 8 = Unknown Failure
 - 9 = Path Not Found
 - 10 = Service Already Running
 - 11 = Service Database Locked
 - 12 = Service Dependency Deleted
 - 13 = Service Dependency Failure
 - 14 = Service Disabled
 - 15 = Service Logon Failure
 - 16 = Service Marked For Deletion
 - 17 = Service No Thread
 - 18 = Status Circular Dependency
 - 19 = Status Duplicate Name
 - 20 = Status Invalid Name
 - 21 = Status Invalid Parameter
 - 22 = Status Invalid Service Account
 - 23 = Status Service Exists
 - 24 = Service Already Paused

Expert Comment

ID: 22900645
In VBS you can open processes loop through them looking for the process.  

http://www.computerperformance.co.uk/vbscript/wmi_process.htm - Code Attached Below

If the process still exists, then alert you and quit script.
' Process.vbs
' Free Sample VBScript to discover which processes are running
' Author Guy Thomas http://computerperformance.co.uk/
' Version 1.4 - December 2005
' -------------------------------------------------------' 
Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strList
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _ 
& strComputer & "\root\cimv2") 
Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process")
For Each objProcess in colProcess
strList = strList & vbCr & _
WSCript.Echo strList
' End of List Process Example VBScript

Open in new window


Author Closing Comment

ID: 31513911
Thank you!

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question