How to restart a service depending on the reseult of query???

Question

How to restart a service depending on the reseult of query???

Kanat Zaiitov

Grasshopper

Points: 22
More actions
June 13, 2006 at 4:50 am

#169163

Hi,
I need a following :
Have a table, and need to test the table every five minutes, like:
select count(origin) from table where (datediff(hh, getdate(), datecol))...
if the result is more that say 100, need to restart a service.
how to make that?
found Alchemy Eye, but cant to make it work.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Probably the best way is through a script outside of SQL Server as there are WMI mechanisms to handle services. However, there is an undocumented stored procedure, xp_servicecontrol, which can be used if the service is on the same server as SQL Server. I wouldn't recommend this approach, though. Another solution, one I also wouldn't recommend, but it has been used, is to execute net stop and then net start through xp_cmdshell. For instance:

EXEC master.dbo.xp_cmdshell "net stop iisadmin /y && net start w3svc"

to stop and restart a web service that is local.

If you want to go with the scripted solution outside of SQL Server, here is the TechNet Script Repository for handling services:

TechNet Script Repository: OS >> Services

There are additional areas for connecting to a database, etc.

K. Brian Kelley
@kbriankelley

Kanat Zaiitov Grasshopper Points: 22 More actions · Answer 2

great help, thank you.

for the given moment I will do second solution.

need to read more

thank you.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

This is something I beleive that could be done with Servers Alive.

http://www.woodstone.nu/salive/features.asp

the free versoin allows 10 checks and can be used without a time limit, for $139 you get 1000 checks. You can monitor just about anything and restarting services is one of the actions you can specifiy when a check fails. I've found this to be invaluable in the past.

To help us help you read this[/url]For better help with performance problems please read this[/url]

kozloski SSC Eights! Points: 804 More actions · Answer 4

Another route would be to use the SQL Server User Settable Object to create a custom Performance Monitor counter. It's fairly well-documented in Books Online.

Create a job that executes your query every five minutes, storing the result in one of the user settable counters:

DECLARE @mycount int

SELECT @mycount = COUNT(origin)

FROM table

WHERE ...

-- Store value in counter object

EXEC sp_user_counter1 @mycount

At this point, you can set up a performance alert that can poll the counter object (SQL Server:User Settable) and run a batch file or other program that can restart the service when the counter value crosses a threshold.

(I work for a non-profit. We have to do things cheap!)

-- J.Kozloski, MCDBA, MCITP