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).