SQL Server monitor
Posted by Karthick P.K on May 11, 2013
Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue?
You can use SQL Monitor to monitor SQL Server instances
SQL Server Monitoring exe monitors the SQL Server services and creates diagnostic data and memory dump if SQL Server service is down (or) If SQL Server is not accepting connections (or) If SQL Server is not responding to Queries
How it works?
SQL Monitor checks the SQL Server in 3-Phases
1. Check the status of all the SQL Server service through the windows service control manager every 60 seconds.
2. If the service is running then check if SQL Server is accepting connections every 60 seconds.
3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.
4. If any of the SQL Server is not accepting Connections then connect to SQL Server using DAC, take a filtered stack dump which will be stored in errorlog directory of the instance , executes custom diagnostic script (c:\sqlmonitor\failoveranalysis.sql) and stores the output in c:\SQLmonitor\ with name “Servername+instancename.txt” which can be used to identify if there is any issue in SQL Server.
5. Once dump is taken release the DAC connection and wait for some time before we attempt to connect again. If connection is successful during subsequent attempt SQLMonitor.exe will continue monitoring the instance but if the connection fails again a new dump is generated and new diagnostic data is collected and appended to Servername+instancename.txt file in SQLMonitor folder.
There will be a gap of X minute between each diagnostic data and stack dump collection when the issue is continuing where X is (Number of Diagnostic data/dump already collected for this instance * Number of Diagnostic data/dump already collected for this instance)
How to Configure?
1. Create a folder called SQLMonitor in C:\
2. Create a Text file called serverlist.txt and enter all the SQL Servers in your environment to be monitored in below format.
Format:
Servername [TAB] Servicename;
Ex:
Server1 MSSQLServer;
Server2 MSSQL$Prod;
3. Invoke command prompt and open SQLmonitor.EXE.
Advantage:
1. Multi-threaded each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.
2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.
3. Uses few MB of memory and system resources.
Requirements:
1. This exe can be invoked from any of the client systems with SQL Server client tools and SQL native clients installed.
2. Remote DAC connection has to be enabled in SQL Servers which are monitored.
3. EXE should be invoked under credential of user who has access to all the SQL Servers which are monitored and permission to view service control manager of windows servers in which SQL Server is running.
You can Download SQLMonitor.exe from this link
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Disclaimer:
The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights
MSSQLWIKI 
Karthik Krishnamurthy (SQL 3T) said
[cid:image001.png@01CE4E39.1FD6B430]
Abhay said
SQLMonitor.exe is not a valid win32 application 🙂
ogloszenia slubne said
I leave a response each time I like a post on a site or I have something
to valuable to contribute to the discussion. It’s caused by the fire
displayed in the article I browsed. And after this post SQL Server monitor MSSQLWIKI.
I was excited enough to post a comment 😉 I actually do have some
questions for you if it’s okay. Could it be only me or does it appear like some
of these remarks come across as if they are written by brain dead individuals?
😛 And, if you are posting on other online sites, I would like to follow anything fresh you have to post.
Would you list every one of your public pages like your linkedin profile, Facebook page or twitter feed?
Andres said
I do not drop a lot of responses, however after looking at a few of the responses on this page SQL Server monitor MSSQLWIKI.
I actually do have a few questions for you if you do not mind.
Could it be only me or does it seem like some of
the responses come across like they are written by brain dead visitors?
😛 And, if you are posting at additional sites, I would like to
keep up with anything new you have to post. Could you list of the
complete urls of all your social networking pages like your
Facebook page, twitter feed, or linkedin profile?