0

Here's our setup :

We are using Red Gate SQL Multi-Script to run queries on our ~1000 databases which reside on a single SQL Server 2008. SQL Multi Script can be configured to run queries in parallel on multiple databases at the same time, which speeds up the execution on this number of databases. We want to run queries as fast as possible so we made the Multi Script to run 25 parallel connections (maximum allowed by Multi Script). SQL Multi Script runs on a desktop computer (Windows 7), and connects to SQL Server 2008 with "sa" login directly via its hostname (myserver.mycompany.com). Our SQL Server 2008 is not a named instance, so we don't reference it with \instancename.

Here's the issue :

We are sometimes getting the following error for a few databases in the 1000, for the same query, same server, same databases, same parallelism (running the same thing again and again, sometimes leads to few errors, sometimes not) :

"Msg 1214, Level 20, State 0, Line 0 A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

One more thing, if we lower the number of parallel connections in Multi Script, those errors seems to disappear, but because we want to have maximum performance from it, we would like to top the number of maximum parallel connections.

I also found this thread on Red Gate forum, but that doesn't help much

UPDATE:

I changed settings in the connection properties to use only TCP/IP, and what I get now is a different error:

"Msg 10048, Level 20, State 0, Line 0 A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)"

Anyone having ideas why that happens ?

MaxiWheat
  • 237
  • 5
  • 12

1 Answers1

0

I would try using TCP/IP only and not Named Pipes.

Greg Askew
  • 35,880
  • 5
  • 54
  • 82
  • 1
    How do you do that ? – MaxiWheat Jan 15 '13 at 19:04
  • Probably depends on the application, and if it is using the default SQL client or the SQL Native Client. Typically when an application establishes a connection, there is some configuration that specifies to use TCP/IP or Named Pipes. It may also be attempting an IP connection, and trying Named Pipes if it fails. Have you tried running this on a server operating system? I would definitely try that before wasting more time on it. – Greg Askew Jan 15 '13 at 19:15
  • I updated my question to include the new error when using TCP/IP only connection. – MaxiWheat Jan 18 '13 at 21:11
  • Time to run a packet capture. – Greg Askew Jan 18 '13 at 21:29