SQL Server“未找到网络路径"跨环境随机和不频繁发生 [英] SQL Server "Network Path Not Found" Randomly and Infrequently Occurring across Environments
问题描述
类似(如果不是相同的问题)随机找不到网络路径异常，但是我有代码来重现该问题，因此我想再问一遍，因为它似乎是独立于硬件的真实问题，可以重现.
这是错误:
提供者:命名管道提供程序，错误:40-无法打开与SQL Server的连接)---> System.ComponentModel.Win32Exception(0x80004005):找不到网络路径 在System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject，UInt32 waitForMultipleObjectsTimeout，布尔值allowCreate，仅布尔值OneCheckConnection，DbConnectionOptions userOptions，DbConnectionInternal& Connection) 在System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
为重现这一点，我创建了一个每分钟运行一次的控制台应用程序(我们也有一个Dapper DAL测试，因此是该参数):
internal class Program
{
private static int _totalOpenConnections;
private static readonly Stopwatch Timer = new Stopwatch();
private static bool _hasError;
private static int Main(string[] args)
{
var list = Enumerable.Range(1, Settings.Default.TotalCommandsToExecute);
// simple ADO.NET test
if (args.Length > 0 && args[0].Equals("ado", StringComparison.OrdinalIgnoreCase))
{
Console.WriteLine("Beginning ADO.NET Test...");
Timer.Restart();
Parallel.ForEach(list, new ParallelOptions {MaxDegreeOfParallelism = Settings.Default.ConcurrentCount},
i => AsyncContext.Run(async () =>
{
try
{
PrintStatus(i);
await TestADONet();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
_hasError = true;
}
}));
Timer.Stop();
Console.WriteLine($"Completed ADO.NET Test in {Timer.ElapsedMilliseconds} ms");
}
if (_hasError)
return 1;
return 0;
}
private static void PrintStatus(int index)
{
Console.WriteLine(
$"Started: {index} of {Settings.Default.TotalCommandsToExecute}\tCurrently Open: {_totalOpenConnections}");
}
private static async Task TestADONet()
{
using (var conn = new SqlConnection(Settings.Default.TestConnection))
{
await conn.OpenAsync();
Interlocked.Increment(ref _totalOpenConnections);
var command = new SqlCommand("SELECT 1 Field1, 2 Field2, 3 Field3", conn);
var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
var result = new TestEntity
{
Field1 = reader.GetInt32(0),
Field2 = reader.GetInt32(1),
Field3 = reader.GetInt32(2)
};
}
}
Interlocked.Decrement(ref _totalOpenConnections);
}
public class TestEntity
{
public int Field1 { get; set; }
public int Field2 { get; set; }
public int Field3 { get; set; }
}
}
应用程序设置ConcurrentCount = 100，TotalCommandsToExecute =200.这个想法是用异步命令并行地严重破坏连接池.
此应用程序复制了它，但是，它也在控制台应用程序，Web应用程序(ASP.NET MVC和ASP.NET WebForms)的生产中发生.
它也相当随机地发生.我们已经让Rackspace和一些DBA在这个问题上无济于事，这最终导致了该应用程序的出现-在开发环境中对其进行了重新制作.
连接字符串比较平淡，格式为数据源=;数据库=;用户ID =;密码="
SQL Server 2014，但这是在两个单独的服务器(dev/rackspace)上发生的
测试中的查询是故意良性的
"SELECT 1 Field1, 2 Field2, 3 Field3"
该测试确实使用Nito.AsyncEx(这里使用的唯一的系统程序集)来获取异步支持.同样，该问题发生在其他未使用该程序集的应用程序中，所以我不认为这是一个问题-如果没有请让我知道，我将以另一种方式重现它.
任何想法都将不胜感激！
问题出在命名管道上.它可能在虚拟机中更多地表达(来自以下链接的推测).通过在连接字符串中添加tcp:并指定端口来使用TCP/IP，可以解决此问题.
一些相关案例:
- https ://dba.stackexchange.com/questions/24165/sql-server-should-we-use-tcp-or-named-pipes-or-use-the-default
- http://devproconnections .com/database-development/sql-server-performance-tip-favoring-tcpip-over-named-pipes
- https://serverfault.com/questions/30955/sporatic-connection-issues-connecting-to-sql-2005-named-pipes-vs-tcp-ip-issue/31024#31024
- https://dba.stackexchange.com/questions /29106/named-pipes-connection-closed-subject to-loaded
结论，始终明确使用TCP/IP，除非SQL Server位于同一台计算机上.您可以将SQL Server配置为也不接受命名管道，但是今后我也将其添加到我的连接字符串中.
Similar (if not same question as) Network path not found exception encountered randomly, but I have code to reproduce the issue so I want to ask again as it appears to be a real issue independent of hardware and can be reproduced.
Here's the error:
provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The network path was not found at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
To reproduce this I created a console application that's run every minute (we also have a Dapper DAL test, hence the parameter):
internal class Program
{
private static int _totalOpenConnections;
private static readonly Stopwatch Timer = new Stopwatch();
private static bool _hasError;
private static int Main(string[] args)
{
var list = Enumerable.Range(1, Settings.Default.TotalCommandsToExecute);
// simple ADO.NET test
if (args.Length > 0 && args[0].Equals("ado", StringComparison.OrdinalIgnoreCase))
{
Console.WriteLine("Beginning ADO.NET Test...");
Timer.Restart();
Parallel.ForEach(list, new ParallelOptions {MaxDegreeOfParallelism = Settings.Default.ConcurrentCount},
i => AsyncContext.Run(async () =>
{
try
{
PrintStatus(i);
await TestADONet();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
_hasError = true;
}
}));
Timer.Stop();
Console.WriteLine($"Completed ADO.NET Test in {Timer.ElapsedMilliseconds} ms");
}
if (_hasError)
return 1;
return 0;
}
private static void PrintStatus(int index)
{
Console.WriteLine(
$"Started: {index} of {Settings.Default.TotalCommandsToExecute}\tCurrently Open: {_totalOpenConnections}");
}
private static async Task TestADONet()
{
using (var conn = new SqlConnection(Settings.Default.TestConnection))
{
await conn.OpenAsync();
Interlocked.Increment(ref _totalOpenConnections);
var command = new SqlCommand("SELECT 1 Field1, 2 Field2, 3 Field3", conn);
var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
var result = new TestEntity
{
Field1 = reader.GetInt32(0),
Field2 = reader.GetInt32(1),
Field3 = reader.GetInt32(2)
};
}
}
Interlocked.Decrement(ref _totalOpenConnections);
}
public class TestEntity
{
public int Field1 { get; set; }
public int Field2 { get; set; }
public int Field3 { get; set; }
}
}
The application settings ConcurrentCount = 100, and TotalCommandsToExecute = 200. The idea is to hit the connection pool pretty hard with async commands in parallel.
This application reproduces it, however, it's also occurred in production in console applications, web applications (ASP.NET MVC, and ASP.NET WebForms).
It also occurs rather randomly. We've had Rackspace and some DBAs crawl through the environment on the issue to no avail, which led to this application - which reproduced it in the development environment.
The connection string is rather bland, of the form "Data Source=;Database=;User Id=;Password="
SQL Server 2014, but this has occurred against two separate servers (dev/rackspace)
The query in the test is intentionally benign
"SELECT 1 Field1, 2 Field2, 3 Field3"
The test does use Nito.AsyncEx, the only none System assembly in use here, to get async support. Again the issue occurs in other applications not using this assembly, so I don't think it's an issue - let me know if not and I'll reproduce it another way.
ANY ideas greatly appreciated!
The issue was with named pipes. It might be expressed more within a VM (speculation from the links below). Using TCP/IP by adding tcp: within the connection string and specifying the port resolved the issue.
Some related cases:
- https://dba.stackexchange.com/questions/24165/sql-server-should-we-use-tcp-or-named-pipes-or-use-the-default
- http://devproconnections.com/database-development/sql-server-performance-tip-favoring-tcpip-over-named-pipes
- https://serverfault.com/questions/30955/sporatic-connection-issues-connecting-to-sql-2005-named-pipes-vs-tcp-ip-issue/31024#31024
- https://dba.stackexchange.com/questions/29106/named-pipes-connection-closed-when-subjected-to-load
Conclusion, always use TCP/IP explicitly unless the SQL Server is on the same machine. You can configure the SQL Server to not accept named pipes as well, but going forward I will just be adding it to my connection strings as well.
这篇关于SQL Server“未找到网络路径"跨环境随机和不频繁发生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
