how to check if SQL Server is running or NOT

Question

how to check if SQL Server is running or NOT

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
June 16, 2009 at 5:22 am

#95426

I have two SQL Servers (Server A and Server B) replicating one database via sql server merge replication.
I add update delete data from any server and data has been replicated among both servers.
I have 3rd SQL Server (Server C) which has a job that update add delete some records from Server A, based on some condition from SQL Server C database.
few days ago Server A was down and the job at Server C started. it becomes fail because of the Server A was not available, but if i make some changes in job that check if Server A is down then i updates data in Server B. so when Server A is on it will automatically synchronize data between them. Ping via xp_cmdshell checks for server availabitlity but i want to check SQL Server services if running then it will start rest of the job activitites otherwise it will try to connect Server B similar way.
How do i do this? checking Server A SQL Server Services running or not using TSQL code in SQL job.
Please help.
Shamshad Ali.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

You can simply read a single row from a guaranteed-to-be-present table and then catch the error if it fails. If it succeeds, then everything is working fine including the pipe in between the two servers.

SELECT TOP 1 1 FROM linkedservername.Master.Sys.Objects

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 2

I think there is another way to check it if its working or not. Sometime ago i read that in a forum and i forgot to save that informartion in my eWallet. I discussed this to my boss that there is a way in SQL Server 2005 to check another server's services (just like ping etc.).

If someone else know that plz. share it to me.

Also the solution provided above need try catch, can you plz. write complete sample code?

Thanks.

Shamshad Ali.

Chand00 SSCertifiable Points: 6466 More actions · Answer 3

Check this

sc query | FIND "STATE"

but requires xp_cmdshell

Thanks

Chandra Mohan N

[font="Verdana"]Thanks
Chandra Mohan[/font]

ankur_libra Hall of Fame Points: 3999 More actions · Answer 4

Hi Shamshad,

You can use the following built in Extended Procedure in Sql Server 2000/2005:

xp_servicecontrol querystate, MSSQLServer

this command will list the status of the sql server.

IIly, you can use it to start and stop these services as fololws:

xp_servicecontrol Start, MSSQLServer

or

xp_servicecontrol Stop, MSSQLServer

For checking SQL Srver Agent service, use MSSqlserverAgent instead of MSSQLServer in above queries.

Cheers..:-P

Paulino PP SSC Veteran Points: 292 More actions · Answer 5

This was very helpful to me. I was able to use this within an SSIS package and send an email notification when command failed.

Paulino PP

forsqlserver SSCoach Points: 18902 More actions · Answer 6

The 'sc query' only gives the running services on system.

Thanks

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 7

Shamshad Ali (6/17/2009)
I think there is another way to check it if its working or not. Sometime ago i read that in a forum and i forgot to save that informartion in my eWallet. I discussed this to my boss that there is a way in SQL Server 2005 to check another server's services (just like ping etc.).
If someone else know that plz. share it to me.
Also the solution provided above need try catch, can you plz. write complete sample code?
Thanks.
Shamshad Ali.

Since you recognized that it needs Try/Catch, I think you also know enough to write it yourself. You've got to have some of the fun. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

glamourth SSC Enthusiast Points: 164 More actions · Answer 8

Hi Paulino PP,

Could you please share information on how did you implement this solution using SSIS.

Thanks.!