SQL Server Connection Error? Sessions not responding.

jnovack

At certain times of the day, I am getting the following error message from ALL my Dynamics NAV clients (at the same time):

Your connection to the full.dns.name server is no longer responding.  This is probably because one of the following reasons:

* The server has been shut down, either manually or as a result of an error.
* Your session has been killed by an administrator.
* A network failure has occurred.
* A hardware failure has occured on the server computer or on your computer.

Navision Backend Server: Windows 2008 Enterprise with SQL Server 2008 Enterprise (Navision Database is 2008)

Now, since it's happening on ALL clients at the same time, I can safely say it's the server which is having the issue. However, I have done at least the following diagnostics:
* I have been on the server (physically) while this has occurred and no "Your network card has disconnected" message came up.
* I have been on the server via Remote Desktop during an outage and no dropping of packets has occurred.
* I have verified from the switch that at no time the link from the network card disconnected and reconnected.

My only guess here is that it's SQL Server being naughty. I have confirmed other "connection pooling" issues with other applications (example of another application complaining: -- A connection was requested from a database connection pool in blocking mode and a timeout occurred. No connection was available to return. )

With that, I have attempted to check the SQL Server connections and at no time did it go above 55.

SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name = 'User Connections'

What else can I check/change to prevent this from happening?

NOTE: This is a "duplicate" of an existing post which has gone nowhere, but wanted to expand on the information. If I was incorrect in starting a new thread, I apologize.

nusyaputera

this is my estimation, these SQL files: .NDF; .MDF; and .LDF file may becoming huge. So, do standard procedures, full database backup; transaction log backup; shrink database; restart server (if needed).

my other estimated cause:
have you try pinging to Navision sever with “-t “. If you found”Request time out” (even only 1 ping) can you check server connection or check your server from a virus. Maybe its happen when a virus (such as w32.downadup or etc.) disturbing your network connection. I have also looking another prevention ways to solve this issues :roll: ..

bbrown

nusyaputera wrote:

this is my estimation, these SQL files: .NDF; .MDF; and .LDF file may becoming huge. So, do standard procedures, full database backup; transaction log backup; shrink database; restart server (if needed)...

Shrinking the database will not help this situation. If anything, it has potential to make it worse. A very large database that is auto-expanding can create this situation. During the auto-expand, the system may not respond to client request in a timely manner.

nusyaputera

If anything, it has potential to make it worse. A very large database that is auto-expanding can create this situation. During the auto-expand, the system may not respond to client request in a timely manner.

:idea: If that so, giving a fixed size to a database (e.g. 50Gb) and disable “automatically file growth” can prevent this issues?

bbrown

nusyaputera wrote:

If anything, it has potential to make it worse. A very large database that is auto-expanding can create this situation. During the auto-expand, the system may not respond to client request in a timely manner.

:idea: If that so, giving a fixed size to a database (e.g. 50Gb) and disable “automatically file growth” can prevent this issues?

And potentially create one of it's own if the client forgets to watch database growth.

Do not rely on Auto-Grow to expand data and transaction log files. Auto-Grow should only be considered a safety net. It is there in case an unexpectedly large process suddenly consumes the available file space. Watch the available space and expand manually during low system activity. Avoid expanding in small increments as this will fragment the file on the drive.

Auto-Grow runs when the free space reaches 0% not as it approaches 0%. It does not say “I’m running out of space. Let me create more before it’s all gone”. What it says is “Oh darn! I ran out of space. Let me take over the system and create some more.”. Unfortunately, it does effectively “take over” the system. While the files are being expanded the system (SQL) will not respond to users. To the users, NAV (SQL) will basically stop responding until the expansion is complete. How long depends on the server’s speed and how much expansion must be done. With larger databases it will get longer. Very large databases can effectively stop responding for several minutes. This is why you don’t depend on Auto-Grow. You want to manage the data size so Auto-Grow never runs.

dmccrae

Often, with the database growth problem, SQL Server will log an entry in the Event Log (e.g. an IO error on the drive where tempdb is). Check the log to see if there is more information there to confirm that this is actually the problem.

nusyaputera

This issues occured to all navision user in my company also.

Navision with SQL database Installed on:
- Windows Server 2003 OS
- SQL server 2000
- Navision 3.7B

User PC:
Uses Navision Client on Windows XP.

and I can't find SQL I/O error.

fredp1

The only time I've seen this happen is when the client looses connection to the server.
I pushed on a fibre connector uplink on a switch for a split second and dropped a connection. ops

A drop in connection for a a few milliseconds will be seen by the client. I wish the Nav/SQL was less sensitive to these glitches.

The error message is a hint, but it can be like finding a needle in a haystack!
It could be a switch on the network, a network card on the server etc.

I would try the easy stuff first, change network cables on the server, plug the server into a different switch/port.
CHeck logs on switches, nic cards etc.


Fred

nusyaputera

..I would try the easy stuff first, change network cables on the server, plug the server into a different switch/port.
CHeck logs on switches, nic cards etc.

hi fred, thank you for your reply..
is this easy stuff can solve the problems? or will be back for some other time?
what i'm trying to say is, this really come from hardware error? :-k

~nusyaputera

fredp1

The problem can be a number of different issues.

Its all about trying to solve one problem at a time. Some times you may have multiple problems that appear as 1 issue.

At my site, I've only had this problem with a network cable glitch. It could be worse with an intermittent network switch, nic etc.

Just try one thing at time and be sure that what you swap out is of good quality. Too many time i've seem people take short cuts with poor equipment/cables.

Good luck.