SQL Server Performance

Check if Sql server is running

Discussion in 'General DBA Questions' started by ndobs, Oct 4, 2005.

  1. ndobs New Member


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

    When the server is stopped then you will get Connection broken error for any queries

    Madhivanan

    Failing to plan is Planning to fail
  3. Adriaan New Member

    <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 />
  4. ranjandba New Member

    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..
  5. mulhall New Member

    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.
  6. ranjandba New Member

    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?

  7. ghemant Moderator

    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 />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami<br />
  8. mulhall New Member

    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.
  9. ranjandba New Member

    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.
  10. Madhivanan Moderator

    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
  11. ranjandba New Member

    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.
  12. satya Moderator

  13. ranjandba New Member

    Satya SKJ,

    Awesome !!!!! thanks a lot.

    this is what exactly i was looking for... let me implement & will update you....
  14. gkrishn New Member

    Ranjan, any updates ? i was tryin with the VBs ,but it is not generating the report file properly.Let me know if any updates?
  15. gkrishn New Member

    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
  16. satya Moderator

  17. gkrishn New Member

    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 .
  18. satya Moderator

    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.
  19. gkrishn New Member

    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.

  20. satya Moderator

    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.
  21. gkrishn New Member

    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
  22. satya Moderator

    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.
  23. gkrishn New Member

    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
  24. mulhall New Member

    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.

Share This Page