Check if Sql server is running | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Check if Sql server is running


How can I check if a Sql server is up and running using Transact-Sql.

When the server is stopped then you will get Connection broken error for any queries Madhivanan Failing to plan is Planning to fail
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />When the server is stopped then you will get Connection broken error for any queries<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">… which is probably why he’s looking for a way to check beforehand, so there are no ugly messages.[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />
Is there any script available which will check from a monitoring server to all connected servers , if sqlserver services are up and running fine on all connected server? To be more specific i’ve 10 servers i want to check if sqlserver services are running in all other machine now i m using manual way ie going to all servers through EM then check agent etc..

Forget the fact it’s SQL for a moment. You want to monitor services, so use a service monitoring tool such as MOM or CIM. If SQL Server is not running you cannot issue T-SQL statements against the server.
mulhall, thanks for the responce .. the purpose of my sarching for a script which can tell me if all the 10 connected servers are up & running is i wanted to automate the things. Its kind of job i will schedule in my monitoring server which will throw me an errr if services is stopped. Right now I have created a job which lets me know if any server is not pinging ie dead. i created a job which executes ping server name. in case it gets error like request time out etc that means the server is down. could any one let me know a utility whcih can let me know if services are running on a remote server by executing a query?
Hi,<br />their are some VB examples are bundled for same in SAMPLES folder (where your sql box folder) , just have a look u will definately find it , it use sqldmo.dll to find / locate available sql servers on network.<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami<br />
You could replace Ping in your existing job with SQLPing or possibly SQLRecon available athttp://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 As I said before T-SQL is the wrong route here, if you want it "in SQL Server" then use a job and/or xp_cmdshell to run the checks.
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 the link is there but there is no option to download the file, can you check the same once again if the link is working properly and gave the valuable informations.
quote:Originally posted by ranjandba http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26 the link is there but there is no option to download the file, can you check the same once again if the link is working properly and gave the valuable informations.
You can see Ratings; Comments; DownLoad option at the bottom of each
Click Download there Madhivanan Failing to plan is Planning to fail
Madhivanan thanks for the responce i could see the download botton and could download the file as well However
To see if sqlserver services are running do i need to execute this utility in command prompt i tried with this but dont get any output. I dont see where i can change the server names could you elaborate the steps to use thyis utility if you’ve used this utility ? What i expect is a utility that i will execute from command prompt like pingsql which will tell me sqlserver service is running
sqlserver agent service is running
other server service is running In case you’ve used this utility could you let me know how to use this utility like if i have 5 sql servers do i need to install this in each one of them?
// SqlPing.cpp
//
// by ChipAndrews, Rajiv Delwadia, Michael Choi
// SQLPing came out of our curiosity and observations of SQL Server’s discovery
// mechanisms. When a SQL Server 2000 client wishes to connect to a server it
// first attempts a pre-connection query against UDP 1434 (unregistered listener
// service on any SQL Server 2000 server). Upon seeing the handshake (packet payload 0x02), the SQL Server
// replies with details about all named instances installed on the server including
// instance name, version, clustering info, net-libs, and net-lib details (ports, pipe
// names, etc.). Using this tool, you can reveal this information as well as send
// discovery packets to entire networks (i.e. 192.168.0.255) for mass interrogation.
// Enjoy.
Checking SQL server heartbeat, referhttp://www.databasejournal.com/features/mssql/article.php/3347241 a valuable resource. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Satya SKJ, Awesome !!!!! thanks a lot. this is what exactly i was looking for… let me implement & will update you….
Ranjan, any updates ? i was tryin with the VBs ,but it is not generating the report file properly.Let me know if any updates?
Hi, once again tried with ‘CHECK HEARTBEAT’ VB script.but output file shows errors like …
10/19/2005 9:22:58 AM
ServerName: ABCD
ServiceName: MSSQLServer
Error: 70
Error: Permission denied
10/19/2005 9:22:58 AM
ServerName: ABCD
ServiceName: SQLSERVERAGENT
Error: 70
Error: Permission denied I triend this from domain user account, and in target server(ABCD) MSSQLServer and SQLSERVERAGENT
services are configured with local system account. Is that the issue here ? –Rajiv
Is that domain account a local admin on ABCD Server? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Yes . But both service startup is using ‘Local system account’ . I am in a venture to implement a monitoring system for SQLserver services on all my boxed(around 30).Any one have this already implemented? Third party tools are not permited here .

Then you better use a LOCAL account rather than a local system account.
You can refer the link suggested above to monitor the heartbeat of SQL server services on your farm, which is a basic step to monitor their status. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Is it not good to use local system account for services? usually there can be change in userIDs in domain/local machines. So to avoid that,i had configured all services with LOCAL SYSTEM ACC.
I mean to say create a local account on the server and dedicate that account to startup SQL Server services. If you use local system account then these problem will crop up. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Yes, i got a valuble info from BOL . Says,
Using the Local System Account
The local system account does not require a password, does not have network access rights in Windows NT 4.0 and Windows 2000, and restricts your SQL Server installation from interacting with other servers.
-Rajiv
True and in your case it seems other servers might need access to this local system privileged SQL Server, correct me if I’m wrong. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Yes you are right.As my services are configured with local system account, its not letting me know its staus from other servers. I will change service to local account or domain account and see if i can run those VBscript fine.
-Rajiv
Remeber that unless you have custom requirements not to grant this account too many permissions: Member of Users only. Not Administrators!! Apply new account details to SQL through Enterprise Manager for the SQL Server and SL Agent Service. This will apply all the local permissions the account need to run SQL. GL.
]]>