Connection Broken

Question

Post reply

Connection Broken

Shah_hs

Ten Centuries

Points: 1117
More actions
July 4, 2007 at 12:11 pm

#68595

Is there a way i can setup an alert/notification for broken sql server connection. I need to get an alert when my sql server is down or there is some problem with the connectivity.
Pls. guide.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 1

Hi Shah,

refer the below query, it will tell whether your sql server and agent is running.

set nocount on

USE master

GO

Print ''

print @@servername

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')

BEGIN

PRINT N'SQL Server is running but SQL Server Agent <<NOT>> running.'

END

ELSE BEGIN

PRINT N'SQL Server and SQL Server Agent both are running.'

END

but it has to be executed from query analyser if ur sql server is down u cant receive notifications....but u can very well run this query from some other server and scheduled it as a job in that server, so that it will execute from that server and chek if the SQL Services are up here.......

[font="Verdana"]- Deepak[/font]

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

net start mssql

If the server is running, you will get an error: the server has been already started. You may start from there.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

How come you will be able to query your server when its not running. You are using a SQL query to find the status of the server its ok if the sql server is up else it will return an error saying sql server doesnot exist. And running from other servers you either need opendatasource or linked servers don't risk that it may lead to bad system performance.

Its better to setup and alert on windows server to check the status of the sql server services.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Shah_hs Ten Centuries Points: 1117 More actions · Answer 4

Thanks guys for all your efforts. Can any one tell me how i can setup alert on windows server to check the status of sql server services. This alternative looks well but i don't know how to setup this alert. Pls. help.

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 5

I approach the problem a little bit differently. I don't just want to know if the SQL server is running or not, I want to know if my customers can see it. So I have a little VB program that accesses actual data just like a customer would, from a machine on located away from the office. Once a minute, it runs a tiny query, and if it can't connect, we get an email (on SMS phones). That way DNS, the firewalls, the switches, and SQL server are all checked out at once.

Student of SQL and Golf, Master of Neither

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

One thing that comes to my mind is create a batch file that sends you a message and the service failure and put that bat file to run on the service failure. yo can check this in the service properties.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Shah_hs Ten Centuries Points: 1117 More actions · Answer 7

Hi, BobAtDBS

Can u help me in creating this script.

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 8

Uh, no I can't. It's not a script. It's a small Visual Basic program, the contents of which are very specific to your Server, what mail OCX you use, and your prefered method of access to sql server (such as ADO, DAO, or one of the .NET methods).

You simply open a connection, run a query (primitive or stored procedure), return a recordset or return parameters, and use the email control to report results. In my opinion, the programming method doesn't matter, use whatever you are using to get data in and out of SQL Server for normal business needs. In fact, the closer your test program is to your normal data access programs, the better.

For our specifics, we use the ADO object model, Visual Basic 6, and the Mabry Mail control for notification.

Student of SQL and Golf, Master of Neither

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 9

A much easier task would be to use Performance Monitor Alerts. An alert can be configured to check for any Perfmon Object ( include SQL Objects ) and execute an action based on the value of the alert. Of course, this would only be useful if SQL Server stops but the OS is still running.

A better alternative would be to purchase enterprise monitoring tool that can monitor services on remote servers.

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

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

The easiest way I've solved this problem has been with a product called ServersAlive. http://www.woodstone.nu/salive/

You can setup 10 checks for free(non expiring trial), or pay a nominal fee for a lifetime license if you have more things to check. $200 gets you 1000 checks, but $300 gets you up to 5000 checks and the ability to write your results out to an ODBC compliant db. Examples of how I use this, I do a simple ping to check if the server is turned on, Then I check the individual SQL services. It can take corrective action for you such as if the SQL service is stopped, restart the service. It also does the whole email/SMS/pages/IM and it also can be set to do an audible alarm if you are located near your servers.

Anyhow I highly recommend it and it's a heck of a lot cheaper than OpenView and has the majority of the functionality.

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