Keep loosing connection

Chriswalton007

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.

Chris Spencer

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

Chriswalton007

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.

Chris Spencer

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

Chriswalton007

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

Chris Spencer

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

Chriswalton007

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

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

Chris Spencer

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

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

Chriswalton007

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