July 22, 2004 at 3:06 pm
I have been trying to find a script to do a simple check with t-sql to to a linked server to check if a database is alive on the linked server. I want to check the database every hour and write results to a log table. I am trying to keep database uptime stats this way. I have found that our network admins will apply SP's or hotfixes to the OS without telling me and they will reboot the server. Does anyone do this already?
--Lori
July 23, 2004 at 2:44 am
there are several tools to do this - although i've found that the best way to do this is to us a bit of VB and use an ADO connection to the database.
as soon as you get a connection failure you start getting messages in the log.
if you are interested in receiving a compiled version (and the source code to go with it) then contact me by email
MVDBA
July 23, 2004 at 7:28 am
No way you can guarantee the linked server is alive when your users access it at anytime. You may log it as alive everytime you check it but users still complain about inaccessable of linked server. You really have to ensure you are informed before network admin does the maintenance to the linked server.
In stead of checking the linked server, Let the linked server to send alive signal to you each server is brought down. We use BMC to do that.
July 23, 2004 at 7:28 am
Through a linked server, any query will do.
if exists( select top 1 * from sysusers) insert logtable
will do it from the source server.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 23, 2004 at 7:42 am
My solution so far has been to take Steve's approach. I made a job that queries a table from the database and logs the time. I set it up to do this every hour. If the database is not available then no new records will be added to the log. My fear is that a reboot could happen between times that I run the query. My plans are to set up another job that will look at the log and if it does not find 24 records for each day then it will send me an alert.
I was hoping to find someone with a more elegant solution. I am waiting for some monitoring software to be purchased by my company but want to try to keep track of database uptime while they are dragging their feet buying the software.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply