Connection problems with SQL Server in ASP.NET applications using out-of-process session state
Transport level errors are often linked to the connection to sql server being broken ... usually network.
Timeout Expired is usually thrown when a sql query takes too long to run.
So I would troubleshoot the link to your Sql Server and then monitor to see what queries are timing out.
Sounds like a SQL job is running, backup? That might be locking tables or restarting the service.
In my case the problem was related with host TCP configuration (virtual machine in VMWare). After quick research (some article found in Google and MSDN Blogs) I've turned off in system registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\synattackprotect (0) and HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\EnableTCPChimney (0).Now it works just fine. Of course this was only test machine (corporate network not visible from Internet) and I wouldn't do that on production environment ;-)
It might be the case in which the length of your query exceeds the limitation of 65,536 *Network Packet Size (default 4KB).