SQL Server Express idle feature causes .NET SQL error: TCP Provider The specified network name is no longer available

By Steve Endow

I just spoke with a partner who is testing Post Master Enterprise, which is a .NET application that automatically posts Dynamics GP batches.

Post Master Enterprise would run fine for a while, but would then log this error:

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) 


On the surface, this is indicating that Post Master is unable to find or connnect with the SQL Server.  This is very strange, since Post Master was working fine, was communicating with the server, and the error seems to occur randomly.

I asked the partner to check the SQL Server logs.  When he opened the logs from SQL Server Management Studio, we saw hundreds of these events.

"Server resumed execution after being idle 1792 seconds: user activity awakened the server"

The time stamp on the events corresponded with the network error that Post Master was receiving.

What in the world does that message mean?  Why would SQL Server be "idle", and since when does SQL need to be "awakened"?

When I searched for this message, I found this MSDN blog post:

http://blogs.msdn.com/b/sqlexpress/archive/2008/02/22/sql-express-behaviors-idle-time-resources-usage-auto-close-and-user-instances.aspx

It appears that SQL Server Express has a "feature" where it will become "idle" after some period of inactivity.  It looks like the feature was added under the guise of performance to somewhat cripple SQL Express.

I asked the GP partner if he was using SQL Server Express in his test environment, and he didn't think so, but when he checked @@VERSION, sure enough, it was Express.

So SQL Server Express was going to sleep, and when Post Master attempted to query the database, it took so long for Express to wake up that Post Master received a network error.

Lesson:  Don't use SQL Express, even for a test environment.

I can't remember the last time I used Express or even saw Express installed anywhere.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com