Background:
The program interrupted “TCPProvider, error: 0-an existing Connection was removed by the remotehost.” There was no problem with manually executing the query, but the execution time was tens of seconds. The execution of the Trace program was found to take tens of minutes, due to the fact that the SqlDataReader was used to read the data and a series of data processing was carried out during the reading process, which made the complete process take a long time. Therefore, it is suspected that some factors such as network instability caused the connection break in the processing process. After coordination, the data was changed to DataTable for one-time data loading. After program adjustment, the frequency of failures was greatly reduced, but it cannot be completely eliminated.
Upon further inspection, the following errors were found in the SQLServer log:
Error: 7884, Severity: 20, State: 1. (Params:). The Error is printed in terse mode because there waserror during formatting. Tracing, ETW, notifications etc are skipped.
This error differs from the standard description in sys.messages and a web search did not find a suitable solution.
Screen:
I directed the table of the query to a server for testing, and finally found that it was related to an NVARCHar (Max) column. Then I remembered that some time ago, I changed the data column of the problem from NTEXT to NVarchar (Max) because of a problem with Logreader, so I specifically tested this change and finally determined that the problem was caused by this change.
(The text/ntext column may cause problems with the logreaderagent, which has been encountered many times, but there is no clear way to reproduce the failure, so we will not discuss this.)
Failure recurrence:
Use the following T-SQL to create the test table, data, and modify the Ntext column NVARCHAR (Max)
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
— Create test tables
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
USE tempdb;
GO
IF OBJECT_ID(N’dbo.tb_test’, ‘U’) IS NOT NULL
DROPTABLE dbo. Tb_test;
GO
CREATE TABLEdbo.tb_test(
Id intIDENTITY PRIMARYKEY,
Code varchar (50),
Datedatetime,
The Value ntext
);
GO
INSERT dbo.tb_test
SELECT TOP(10000)
Code =RIGHT(10000000000 +ABS(CHECKSUM(NEWID())) % (1000 * 2), 20),
Date= DATEADD(DAY, CHECKSUM(NEWID()) % 100, GETDATE()),
Value = the CONVERT (char (36), NEWID ())
FROM sys.all_columns A WITH(NOLOCK)
, sys. All_columns WITH B (NOLOCK)
;
GO
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
— Change the ntext field type to NVARCHAR (Max)
– = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
ALTER TABLEdbo.tb_test ALTERCOLUMN Value nvarchar(max);
GO
Write the program, query the data, and change the data before the query is completed, which is tested with the PowherShell
# connection string
$ConnectionString = “Data Source = 127.0.0.1; InitialCatalog=tempdb; Integrated Security=SSPI”
# Open connection
$SqlCnnectionQuery = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
$SqlCnnectionQuery.Open()
# Query data
$SqlCommandQuery = New-Object System.Data.SQLClient.SQLCommand
$SqlCommandQuery.Connection = $SqlCnnectionQuery
$SqlCommandQuery.CommandText = “SELECT * FROM( SELECT value, row_id = ROW_NUMBER()OVER( PARTITION BY Code ORDER BY date DESC) FROM dbo.tb_test WITH(NOLOCK) )DATAWHERE row_id = 1”
$SqlReader = $SqlCommandQuery.ExecuteReader()
# Modify data
$SqlCnnectionUpdate = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
$SqlCnnectionUpdate.Open()
$SqlCommandUpdate = New-Object System.Data.SQLClient.SQLCommand
$SqlCommandUpdate.Connection = $SqlCnnectionUpdate
$SqlCommandUpdate.CommandText = “UPDATE top(1000) dbo.tb_test SET Value =CONVERT(char(36), NEWID() ) WHERE id IN( SELECT TOP 1000 id FROM dbo.tb_testORDER BY id DESC )”
$UpdateRows = $SqlCommandUpdate.ExecuteNonQuery()
$SqlCnnectionUpdate.Close()
“Update $UpdateRows rows.”
# Read query data
An error occurred during the reading process
# (SQL 2008 R2 SP2) : TCP Provider, error: 0-anexisting Connection was removed by the remote host.
# (SQL 2008 R2 SP3) : TCP Provider, Error: 0 – The SpecifiedNetwork Name is no longer available.
At line:1 char:22
“Read query data….”
$ReadRows=0
While($SqlReader.Read()) {$ReadRows+=1}
“Read $ReadRows rows.”
# Close connection
$SqlReader.Close()
$SqlCnnectionQuery.Close()
Fault handling:
For columns with modified data types, the problem can be solved by reupdating the data (UPDATE table SET modified columns = modified columns)
This problem has been tested from SQL Server2008 to 2014, there are problems. In the test of SQL Server 2008 R2 SP3, error information in THE SQL Server log is different, as follows:
Error: 7886, Severity: 20, State: 2.
A read operation on a large object failedwhile sending data to the client. A common cause for this is if the applicationis running in READ UNCOMMITTED isolation level. This connection will beterminated.