C＃ASP.Net：在长数据库操作期间的异常（仅限某些时间） [英] C# ASP.Net: Exception (some times only) during long database operation
问题描述
我们有一个使用WCF连接到其业务层的ASP.Net Web应用程序。有时候，当我执行一个巨大的操作时，我有异常。有趣的部分是当我第一次成功运行它时。之后它是抛出异常。
异常：套接字连接已中止。
操作是将邮政编码从csv文件上传到数据库表中。首先我们从csv文件中读取并制作一个冗长的邮政编码字符串。这被传递到存储过程，并且执行数据库操作。
注1）当我放置断点并进行测试时，很明显，创建字符串（从csv获取数据并附加后）很快（小于一分钟）。
注2 :)我删除了交易（从C＃代码）并进行了测试，即使是例外。
注3 :) csv中有一个lakh（十万）条记录。每行有四列（ZipCode，City，County，State）。 csv文件大小为3MB。
我对交易日志大小有疑问。然后我使用以下命令收缩日志文件。
DBCC SHRINKFILE（'MyDB_log'，1）GO
然后我使用SELECT [Size]，Max_Size，Data_Space_Id，[File_Id]，Type_Desc检查日志大小，[名称] FROM FRAMIS_R2075.sys.database_files WHERE data_space_id = 0
大小为128;最大尺寸为268435456; Type_Desc =LOG
即使缩小后，异常仍然来临。
框架：.Net 3.0
DB：SQL Server 2005
嗯，似乎在业务层也有一个兴趣，我等了20多分钟。它说：读者被叫时尝试调用Read。看到这个，我删除了DbDataReader并使用SqlCommand来更新数据库表。再次，业务层出现异常，大概20分钟后就出现了超时异常。任何想法为什么会发生这种情况？
private void ProcessDatabaseOperationsForZipCode（StringBuilder dataStringToProcess，int UserID）
{
int CountOfUnchangedZipCode = 0;
string strRetiredZipCode =;
string strNewZipCode =;
dataStringToProcess.Remove（dataStringToProcess.Length - 1，1）;
if（dataStringToProcess.Length> 0）
{
//TimeSpan.FromMinutes(0） - 使事务范围为无限大。
使用（TransactionScope transaction = TransactionScopeFactory.GetTransactionScope（TimeSpan.FromMinutes（0）））
{
SqlConnection mySqlConnection = new SqlConnection（data source = myServer;初始目录= myDB;集成安全= SSPI;）;
SqlCommand mySqlCommand = new SqlCommand（aspInsertUSAZipCode，mySqlConnection）;
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add（@ DataRows，dataStringToProcess.ToString（））;
mySqlCommand.Parameters.Add（@ currDate，DateTime.Now）;
mySqlCommand.Parameters.Add（@ userID，UserID）;
mySqlCommand.Parameters.Add（@ CountOfUnchangedZipCode，1000）;
mySqlCommand.CommandTimeout = 0;
mySqlConnection.Open（）;
int numberOfRows = mySqlCommand.ExecuteNonQuery（）;
//数据库db = DatabaseFactory.CreateDatabase（）;
// DbCommand cmd = db.GetStoredProcCommand（aspInsertUSAZipCode）;
//cmd.CommandTimeout = 0;
//db.AddInParameter(cmd，@DataRows，DbType.String，dataStringToProcess.ToString（））;
//db.AddInParameter(cmd，currDate，DbType.DateTime，DateTime.Now）;
//db.AddInParameter(cmd，userID，DbType.Int32，UserID）;
//db.AddOutParameter(cmd，CountOfUnchangedZipCode，DbType.String，1000）;
//使用（DbDataReader rdrUpgradeTypes =（DbDataReader）db.ExecuteReader（cmd））
// {
// // while（rdrUpgradeTypes.Read（ ））
// // {
// // if（！String.IsNullOrEmpty（Utility.GetString（rdrUpgradeTypes，NewZipCode）））
// // {
// // strNewZipCode = strNewZipCode +，+ Utility.GetString（rdrUpgradeTypes，NewZipCode）;
// //}
// //}
//}
transaction.Complete（）;
}
}
}
感谢Oded耐心回答我的问题。当我将巨大的数据库操作分解成小批量（配置MSDTC后），我的问题得到解决。
在大数据库操作期间，内存错误可能不足。但是，如果是在事务内部，则此错误可能不可见。
1）删除事务并查看内存不足异常是否可用。
2）将大型数据库操作分解成小批量以删除内存不足异常
3）在应用层和数据库中配置MSDTC
4）确保WCF不会突然超时。它应该有足够的时间基于数据库操作。
5）观察数据库服务器重新启动时的行为（这是最后一个选项）
C＃ASP .Net：长数据库操作期间的异常（仅限某些时间）
有些o其他检查：
1）数据库引擎运行的Windows帐户是否具有在内存中锁定页面权限？
2）Chek SQL Sever Service Pack版本。
3）检查虚拟内存页面文件的大小
5）确定MemToLeave设置
6）SQL Server内存配置和MemToLeave
We have a ASP.Net web application that connects to its business layer using WCF. Sometimes, I am getting exception when it is performing a huge operation. The interesting part is when I ran it for the first time it was successful. Afterwards it is throwing exception.
Exception: The socket connection was aborted.
The operation is that it uploads zipcodes into database tables from a csv file. First we read from csv file and make a single lengthy string of zipcodes. This is passed to the stored procedure and the database operations are performed.
Note 1:) When I placed breakpoint and tested, it is clear that the creation of the string (after taking data from csv and appending) is pretty fast (less than one minute).
Note 2:) I removed the transaction (from C# code) and tested, even then the exception is there.
Note 3:) There are one lakh (one hundred thousand) records in csv. Each row has four columns (ZipCode, City, County, State). Size of the csv file is 3MB.
I had a doubt about the transaction log size. Then I shrinked the log file using the following command. DBCC SHRINKFILE('MyDB_log', 1) GO
Then I checked the log size using SELECT [Size],Max_Size,Data_Space_Id,[File_Id],Type_Desc,[Name] FROM FRAMIS_R2075.sys.database_files WHERE data_space_id = 0
The Size is 128; Max Size is 268435456; Type_Desc = "LOG"
Even after shrinking the exception is still coming.
Framework: .Net 3.0
DB: SQL Server 2005
Well, it seems like there is an excpetion in the business layer too, when I waited for 20 more minutes. It said, "Invalid Attempt to call Read when the reader is called". By seeing this, I removed the DbDataReader and used SqlCommand to update the database tables. Again, there came an exception in business layer, after some 20 minutes saying "Timeout exception". Any idea why this is happening?
private void ProcessDatabaseOperationsForZipCode(StringBuilder dataStringToProcess, int UserID)
{
int CountOfUnchangedZipCode = 0;
string strRetiredZipCode = "";
string strNewZipCode = "";
dataStringToProcess.Remove(dataStringToProcess.Length - 1, 1);
if (dataStringToProcess.Length > 0)
{
//TimeSpan.FromMinutes(0) - to make transaction scope as infinite.
using (TransactionScope transaction = TransactionScopeFactory.GetTransactionScope(TimeSpan.FromMinutes(0)))
{
SqlConnection mySqlConnection = new SqlConnection("data source=myServer;initial catalog=myDB; Integrated Security=SSPI;");
SqlCommand mySqlCommand = new SqlCommand("aspInsertUSAZipCode", mySqlConnection);
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add("@DataRows",dataStringToProcess.ToString());
mySqlCommand.Parameters.Add("@currDate", DateTime.Now);
mySqlCommand.Parameters.Add("@userID", UserID);
mySqlCommand.Parameters.Add("@CountOfUnchangedZipCode", 1000);
mySqlCommand.CommandTimeout = 0;
mySqlConnection.Open();
int numberOfRows = mySqlCommand.ExecuteNonQuery();
//Database db = DatabaseFactory.CreateDatabase();
//DbCommand cmd = db.GetStoredProcCommand("aspInsertUSAZipCode");
//cmd.CommandTimeout = 0;
//db.AddInParameter(cmd, "@DataRows", DbType.String, dataStringToProcess.ToString());
//db.AddInParameter(cmd, "currDate", DbType.DateTime, DateTime.Now);
//db.AddInParameter(cmd, "userID", DbType.Int32, UserID);
//db.AddOutParameter(cmd, "CountOfUnchangedZipCode", DbType.String, 1000);
//using (DbDataReader rdrUpgradeTypes = (DbDataReader)db.ExecuteReader(cmd))
//{
// //while (rdrUpgradeTypes.Read())
// //{
// // if (!String.IsNullOrEmpty(Utility.GetString(rdrUpgradeTypes, "NewZipCode")))
// // {
// // strNewZipCode = strNewZipCode + "," + Utility.GetString(rdrUpgradeTypes, "NewZipCode");
// // }
// //}
//}
transaction.Complete();
}
}
}
Thanks Oded for patiently answering my questions. My problem got resolved when I splitted the huge database operations into small batches (after configuring MSDTC).
During Large Database operations, insufficient memory error may come. But this error may not be visible if it is inside a Transaction.
1) Remove transaction and see if "Insufficient Memory" exception is available.
2) Breakdown large database operations into small batches to remove the "Insufficient Memory" exception
3) Configure MSDTC in app tier and database
4) Ensure that WCF does not time out abruptly. It should have enough time based on database operations.
5) Observe the behavior when the database server is restarted (This is last option)
Some useful information available in
There is insufficient system memory in resource pool 'default' to run this query
MSDTC Exception during Transaction: C#
C# ASP.Net: Exception (some times only) during long database operation
SQL Server 2005 Error 701 - out of memory
Some other checks:
1) Whether the windows account under which the database engine is running have "Lock pages in memory permission"?
2) Chek SQL Sever service pack version.
3) Check size of virtual memory paging file
4) Check max server memory
5) Determining MemToLeave Settings
6) "SQL Server Memory Configuration and MemToLeave"
这篇关于C＃ASP.Net：在长数据库操作期间的异常（仅限某些时间）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
