How to monitor a windows service (not just default status but to check whether it is WORKING (working on a database table or not))

Background: I have a developed a windows service which run every d开发者_高级运维ay at 12pm (calls an so and do its work).

Now I have a web application - one of its operation is "To call an sp and regenerate the data" (the sp is the same one which windows service is calling) So, I have to make sure before calling that sp from web application that the windows service has not called the sp at that time or is not working on that db table .. other wise there would be some consequences..

How can I achieve this thing? Your help is highly appreciated!

Thanks in advance.

---

Implement proper locking in the database. Either using table / row locks or using a custom locked objects table.

---

Firstly, having a stored proc that can only be called by a single client at any time might be a bad thing - suggests your transaction management may be suspect.

However, there are legitimate reasons for doing this - e.g. if the stored proc consumes a lot of system resources.

I've done this by creating a "batches" table, roughly as follows:

batchid   start_date    end_date   current_status

When the proc starts, it inserts a record in this table with a NULL end date and "Started" current_status; when it completes, it updates that record with end_date and "finished" current_status.

You can then insert a check at the start of the proc and have it return immediately if it finds a record in the "batches" table with a current_status of "started".

You have to make sure all exit points from the proc update the current_status field - otherwise your proc will never run after an error causes the proc to abort.

Ugly, but effective.

---

You might want to look at wrapping the work inside this stored proc in calls to sp_getapplock and sp_releaseapplock.

On the other hand, it's generally better to eliminate whatever is requiring you to serialize access to the procedure.

---

E.g.:

CREATE PROCEDURE doStuff
AS
    DECLARE @rc int;
    EXEC @rc = sp_getapplock 'doStuff','Exclusive','Session';

    IF @rc < 0 return;

    --Do interesting things

    EXEC sp_releaseapplock 'doStuff', 'Session'