SQL Server Performance Forum – Threads Archive

Server Pings

Hello, I am posting this question in this forum because, going by the descriptions of the other, it did not match up with any other. Also, I think you guys have excellent input from a technical standpoint versus other message boards, so I thought I’d give it a shot here first. I have a strange failure in the middle of the night that I suspect is due to network traffic, so I am running a script with the following simple code: ping -t myserver1 >>e:mydrive1pingdocument.txt I am running the above on "myserver2". My goal is to see the ping results from server to server and to store the ping results to a text file. Well, the above has been successful as per my specifications. I am collecting the ping statistics to a text file and am analyzing in MS Access. However, I do not get a timestamp in the results, so if I see an issue, I have no idea what time it is at. I am doing simple division based on the start and stop time and the record # of the issue. I guess my question is two fold. 1. Does anyone know if and how I can get the timestamp for ach ping result in my log file? 2. Does anyone know an easier way for me to determine what the network traffic is during my nightly refresh? Please keep in mind that I am a beginner here, as you can see the above process that I created is very simple. Also, I am not able to purchase any third party software, but am willing to use freeware. This topic is only indirectly related to SQL server. My process is from a Cognos application pulling data from SQL server and uses .tmp files during the build process. Again, I am only suspecting that the error is from network traffic or a spike, and if it happens again, I want to be armed with statistics during the night for proof. Any thoughts? Any input or discussion is greatly appreciated. Thanks. Rodney

The first thing I would do is look at performance counter on the servers involved. Look at the network counters to see what the queue lengths are like and if you’re getting any errors. Also, look at disk queue lengths, process, and memory utilization to see how the servers are performing. If any of these are messed up, focus in on the particular problem area. Don’t just assume it’s a network error. There are lots of variables in troubleshooting issues like this.
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Refer to event viewer, sql server and Cognos application log for further information.
If everything is ideal at SQL server side then we immediately suspect it may be due to network, but its worth also whether any scheduled jobs are overlapping and causing this disconnections.
HTH 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.

You can use the following to get a timestamp on your ping in Win2k/Win2k3 You need 3 bat files and a sleep.exe or some other delay command. You call it with two input paramters: address and output file C:&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ingtrace.bat www.google.com c

ingtrace.txt pingtrace.bat<pre id="code">@ECHO OFF ECHO Pingtrace running… (terminate with CTRL-C) :loop call pingtrace_sub1.bat %1 %2 sleep 1 goto loop :end </pre id="code"> pingtrace_sub1.bat<pre id="code">@ECHO OFF :: Clear variables set pingoutput= :: Call a seperate ping file since FOR can not handle | for /F "delims=" %%i in (‘pingtrace_sub2.bat %1’) do set pingoutput=%%i if "%pingoutput%"=="" ( echo %date% %time% "No Reply" >> %2 ) ELSE ( echo %date% %time% "%pingoutput%" >> %2 ) :: Clean up set pingoutput= </pre id="code"> pingtrace_sub2.bat<pre id="code">@ECHO OFF ping.exe -n 1 %1 | find "Reply from" </pre id="code">

Satya, Thanks. I checked the event viewer and see that a scan is being performed at 3am, during the exact time I am having the issues. I will see if I can change the time of this scan. I do not see anything in the SQL server logs or the Cognos application log. I also may have been low on disk space for those errors, but there still should have been enough to not cause a failure. Derrick, I will look into collecting the network counters, thanks. The servers are performing well as far as memory utilization. Disk Queue is misleading for me because my servers are not dedicated to SQL. I have Cognos applications running overnight. Argyle, Thanks for the .BAT script post. I will try to test what you are suggesting. Instead of ‘sleep’ commands in my scripts, I use the following to pause a certain period of time: ping -n 2 -w 10000 1.1.1.1 I use the 1.1.1.1 so this ping produces a timeout and the other parameters to do it 2 times every 10000ms. Thank you, all of you, for your input here. It is greatly appreciated!

Argyle, UNBELIEVABLE!! That script you outlined absolutely works like a charm. I made some slight adjustments. Instead of a sleep command, I used ping -n 1 -w 5000 1.1.1.1 which produces a log every 5 seconds or so. Also, I automated this by creating a 4th script with the following:
——————————
E: cd E:folder1folder2folder3
pingtrace.bat server1 E:folder4pingtrace_server1.txt
—————————— THis way, I can trigger this script remotely with Batch server or I can schedule from the scheduler so I don’t have to manually launch this every day. As an added enhancement to this (only if it is simple to do ), does anyone know how to automatically stop this loop at a certain time or after a certain number of trips? THis will fully automate this. Right now, I will have to open the server and ctrl-c the processes to stop, but if I could simply tell it to stop at 6am or after 100,000 pings, this would be great, too. Anyway, thanks for the help, I am just amazed at how well this works. Also, I have really no idea how this works, but it is still cool. Rodney

Glad it works. Took a while to put together. If you could return the process ID of the cmd.exe running the bat file you could always schedule another bat file with a kill.exe command in it killing that process id at 6am

Argyle, Thanks again. Believe it or not, setting the windows scheduler "stop this task if it runs for more than" time to 8 hours automatically stopped the pinging for me. This is strange that it worked for this because the way I was running a task from the windows scheduler before I changed to the process you gave me, it was not stopping the task.

quote:
Derrick, I will look into collecting the network counters, thanks. The servers are performing well as far as memory utilization. Disk Queue is misleading for me because my servers are not dedicated to SQL. I have Cognos applications running overnight.

These are still very applicable. If you have to, break down write and read queue lengths by specific disks to isolate it to SQL Server. You NEED to be looking at these counters if you’re troubleshooting overall performance issues. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>