Keep loosing connection

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Keep loosing connection

Postby Chriswalton007 » Fri Jan 27, 2012 10:52 am

HI, I seem to keep loosing connect to my monitored sql server, the error message shows as:


Date Group Event Outcome Exception Exception message
27 Jan 2012 9:50 AM SQL Ping Connected
27 Jan 2012 9:50 AM SQL Ping Connected
27 Jan 2012 9:50 AM SQL Ping Connected
27 Jan 2012 9:50 AM SQL Ping Connected
27 Jan 2012 9:49 AM SQL Ping Connected
27 Jan 2012 9:49 AM SQL Ping Connected
27 Jan 2012 9:49 AM Registry GetBinaryValue: \\\\klamath.dedi.melbourne.co.uk\\HKEY_PERFORMANCE_DATA\\238 230 Connected
27 Jan 2012 9:49 AM SQL Ping Connected
27 Jan 2012 9:49 AM Registry OpenRegistryHive: PerformanceData Connected
27 Jan 2012 9:49 AM SQL Ping Connected
27 Jan 2012 9:49 AM SQL #YyK Connected
27 Jan 2012 9:49 AM SQL Get Server Time Connected
27 Jan 2012 9:49 AM SQL Ping Connected
27 Jan 2012 9:49 AM Registry GetBinaryValue: \\\\klamath.dedi.melbourne.co.uk\\HKEY_PERFORMANCE_DATA\\7606 7352 7492 7424 7590 7474 7762 7698 Connected
27 Jan 2012 9:49 AM Registry OpenRegistryHive: PerformanceData Connected
27 Jan 2012 9:49 AM SQL Ping Connected
27 Jan 2012 9:48 AM SQL Ping Connected
27 Jan 2012 9:48 AM File Open: C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG Connected
27 Jan 2012 9:48 AM SQL Ping Connected
27 Jan 2012 9:48 AM SQL Ping Connected
27 Jan 2012 9:48 AM SQL Ping Connected
27 Jan 2012 9:48 AM SQL GetErrorLogPath Connected
27 Jan 2012 9:48 AM SQL #aKv Connected
27 Jan 2012 9:48 AM SQL Ping Connected
27 Jan 2012 9:48 AM Registry GetBinaryValue: \\\\klamath.dedi.melbourne.co.uk\\HKEY_PERFORMANCE_DATA\\238 230 Connected
27 Jan 2012 9:48 AM SQL Ping Connected
27 Jan 2012 9:48 AM Registry OpenRegistryHive: PerformanceData Connected
27 Jan 2012 9:48 AM SQL #zX7 Connected
27 Jan 2012 9:47 AM SQL #YyK Connected
27 Jan 2012 9:47 AM SQL Ping Connected
27 Jan 2012 9:47 AM SQL #2Kv Connected
27 Jan 2012 9:47 AM SQL Get Server Time Connected
27 Jan 2012 9:47 AM Registry GetBinaryValue: \\\\klamath.dedi.melbourne.co.uk\\HKEY_PERFORMANCE_DATA\\7606 7352 7492 7424 7590 7474 7762 7698 Connected
27 Jan 2012 9:47 AM SQL Ping Connected
27 Jan 2012 9:47 AM Registry OpenRegistryHive: PerformanceData Connected
27 Jan 2012 9:47 AM File Open: C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG Connected
27 Jan 2012 9:47 AM SQL Ping Connected
27 Jan 2012 9:47 AM SQL Get Server Time Connected
27 Jan 2012 9:47 AM SQL #4Jv Connected
27 Jan 2012 9:47 AM SQL Ping Connected
27 Jan 2012 9:47 AM SQL #nKv Connected
27 Jan 2012 9:47 AM SQL #IKv Connected
27 Jan 2012 9:47 AM SQL Ping Connected
27 Jan 2012 9:47 AM SQL #LVG Connected
27 Jan 2012 9:47 AM SQL Ping Connected
27 Jan 2012 9:47 AM SQL GetErrorLogPath Connected
27 Jan 2012 9:47 AM SQL #aKv Connected
27 Jan 2012 9:46 AM Registry GetBinaryValue: \\\\klamath.dedi.melbourne.co.uk\\HKEY_PERFORMANCE_DATA\\238 230 Connected
27 Jan 2012 9:46 AM SQL Ping Connected
27 Jan 2012 9:46 AM SQL Ping Connected
27 Jan 2012 9:46 AM Registry OpenRegistryHive: PerformanceData Connected
27 Jan 2012 9:46 AM SQL #YyK Connected
27 Jan 2012 9:46 AM SQL Get Server Time Connected
27 Jan 2012 9:46 AM Registry GetBinaryValue: \\\\klamath.dedi.melbourne.co.uk\\HKEY_PERFORMANCE_DATA\\7606 7352 7492 7424 7590 7474 7762 7698 Connected
27 Jan 2012 9:46 AM SQL Ping Connected
27 Jan 2012 9:46 AM Registry OpenRegistryHive: PerformanceData Connected
27 Jan 2012 9:46 AM SQL Ping Connected
27 Jan 2012 9:46 AM File Open: C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\ERRORLOG Connected
27 Jan 2012 9:46 AM SQL Ping Connected
27 Jan 2012 9:46 AM File Delete: F:\\SQLLOG\\20120127084542-00-DEST051-MONITOR.trc Cannot connect IOException The process cannot access the file '\\\\klamath.dedi.melbourne.co.uk\\F$\\SQLLOG\\20120127084542-00-DEST051-MONITOR.trc' because it is being used by another process.
Chriswalton007
 
Posts: 13
Joined: Fri Jan 27, 2012 10:47 am

Postby Chris Spencer » Fri Jan 27, 2012 12:10 pm

Hi

SQL Monitor appears to be having problems trying to delete the trace files that it creates. Some process (possibly SQL Monitor) is still reading/writing from the .trc file we're trying to delete.

Does it still have a connection issue when trace is turned off? This can be done in Configuration > Trace.

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Chriswalton007 » Fri Jan 27, 2012 12:42 pm

Hi Chris,

I did recently enable trace, as I needed the information for deadlocks which were happening. The issue did not seem to happen as often prioir to enabling this, however I really don't want to turn it off as I need the deadlock information.
Chriswalton007
 
Posts: 13
Joined: Fri Jan 27, 2012 10:47 am

Postby Chris Spencer » Fri Jan 27, 2012 1:14 pm

Is it always the same file (i.e. F:\\SQLLOG\\20120127084542-00-DEST051-MONITOR.trc) that is causing the error?

Is it possible to delete this file manually? Maybe also try switching trace off, waiting five minutes and switching it on again?

Is it possible to run this query against the server in question to get some information on any running traces?

SELECT * FROM :: fn_trace_getinfo(default)

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Chriswalton007 » Fri Jan 27, 2012 1:18 pm

THe file no longer exisits there, I rebooted the machine which the monitor sits on and the file went.

traceid property value
1 1 2
1 2 C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\log_896.trc
1 3 20
1 4 NULL
1 5 1
3 1 2
3 2 F:\\SQLLOG\\20120127121310-00-DEST051-MONITOR.trc
3 3 250
3 4 2012-01-27 12:19:10.383
3 5 1
Chriswalton007
 
Posts: 13
Joined: Fri Jan 27, 2012 10:47 am

Postby Chris Spencer » Fri Jan 27, 2012 1:55 pm

Are you still seeing these connection issues now that that particular file has gone? If so I'll ask the developer responsible for the trace implementation if he has any ideas.

More generally, I don't think the connection should be lost just because of an inablility to delete a file. I'll investigate this issue separately and raise a bug if need be.

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Chriswalton007 » Fri Jan 27, 2012 3:06 pm

Hi Chris,

have not seen it since the reboot, however it often happens that it comes back a few days later. Will keep an eye out and if it happens again will repost on this thread.

Thanks for your help
Chriswalton007
 
Posts: 13
Joined: Fri Jan 27, 2012 10:47 am

Postby Chriswalton007 » Fri Jan 27, 2012 5:44 pm

CHris,

I have started expereincing the problem again. Same error message as before (although pointing to a different trace file). THe outcome is still the same in that it says could not connect.

I have turned off trace on the servers for now, but could you ask on of the developers to look at this, as ideally I need trace on.

Thanks,
Chris
Chriswalton007
 
Posts: 13
Joined: Fri Jan 27, 2012 10:47 am

Postby Chris Spencer » Mon Jan 30, 2012 3:17 pm

Hi Chris

I've spoken to the developer about this and confirmed that there are two likely reasons for the error:
1. SQL Server is still writing to the file
2. SQL Monitor is still reading the file (we only read from the trace files when an alert comes along that can be associated with trace data - like deadlocks or long-running query alerts)

It's possible to check this using a tool like Process Monitor: ( http://technet.microsoft.com/en-us/sysi ... s/bb896645 ) but I'm doubtful that it would help diagnose the problem.

A couple of questions:
1. Are there any clockskew issues between the base monitoring machine and the SQL Server being monitored?
2. Are there any performance issues on the SQL Server being monitored or the base monitoring machine?

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Chris Spencer » Mon Jan 30, 2012 3:44 pm

Hi

Also would it be possible to see a screenshot of the connection error on the Monitored Servers page? It should state what kind of error it is. You can send the screenshot to chris.spencer@red-gate.com if that's easier for you.

Also what version of SQL monitor are you using?

Regards
Chris
Chris Spencer
Test Engineer
Red Gate
Chris Spencer
 
Posts: 301
Joined: Tue Aug 29, 2006 9:09 am
Location: Red Gate - Cambridge

Postby Chriswalton007 » Fri Feb 10, 2012 10:51 am

Hi Chris,

Sorry for the delay in getting back to you about this, have been very busy.

In answer to your questions...

Yes sometimes there are clockskew issues between the base monitor server and the monitored server. I will ask the network guy if he can look at this now that it could possibly be a cause.

No performance issues on either server.

I will turn the trace back on and then send a screen shot when the error next appears.

Thanks,
Chris
Chriswalton007
 
Posts: 13
Joined: Fri Jan 27, 2012 10:47 am


Return to SQL Monitor 2

Who is online

Users browsing this forum: No registered users and 2 guests