Timeout Expired The timeout period elapsed prior to completion
-
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on cmdSQL.ExecuteNonQuery() Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it. Your immediate response will be higly appreciated. Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean Dim cmdSQL As SqlClient.SqlCommand Dim blnReturn As Boolean = False Dim objParam As SqlClient.SqlParameter Dim arrData() As Byte Try If Not IO.File.Exists(strFileName) Then Throw New Exception("PDF file not found:" & strFileName) Else arrData = ReadFile(strFileName) End If cmdSQL = New SqlClient.SqlCommand("UPDATE [] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("", Me.Job.JobName), Me.m_cnnSQL) cmdSQL.CommandType = CommandType.Text objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1) objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image) objParam.Value = arrData objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat") cmdSQL.ExecuteNonQuery() cmdSQL.Dispose() blnReturn = True Catch ex As Exception Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly) Finally If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName) cmdSQL = Nothing End Try Return blnReturn End Function
-
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on cmdSQL.ExecuteNonQuery() Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it. Your immediate response will be higly appreciated. Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean Dim cmdSQL As SqlClient.SqlCommand Dim blnReturn As Boolean = False Dim objParam As SqlClient.SqlParameter Dim arrData() As Byte Try If Not IO.File.Exists(strFileName) Then Throw New Exception("PDF file not found:" & strFileName) Else arrData = ReadFile(strFileName) End If cmdSQL = New SqlClient.SqlCommand("UPDATE [] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("", Me.Job.JobName), Me.m_cnnSQL) cmdSQL.CommandType = CommandType.Text objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1) objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image) objParam.Value = arrData objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat") cmdSQL.ExecuteNonQuery() cmdSQL.Dispose() blnReturn = True Catch ex As Exception Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly) Finally If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName) cmdSQL = Nothing End Try Return blnReturn End Function
-
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on cmdSQL.ExecuteNonQuery() Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it. Your immediate response will be higly appreciated. Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean Dim cmdSQL As SqlClient.SqlCommand Dim blnReturn As Boolean = False Dim objParam As SqlClient.SqlParameter Dim arrData() As Byte Try If Not IO.File.Exists(strFileName) Then Throw New Exception("PDF file not found:" & strFileName) Else arrData = ReadFile(strFileName) End If cmdSQL = New SqlClient.SqlCommand("UPDATE [] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("", Me.Job.JobName), Me.m_cnnSQL) cmdSQL.CommandType = CommandType.Text objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1) objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image) objParam.Value = arrData objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat") cmdSQL.ExecuteNonQuery() cmdSQL.Dispose() blnReturn = True Catch ex As Exception Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly) Finally If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName) cmdSQL = Nothing End Try Return blnReturn End Function
-
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on cmdSQL.ExecuteNonQuery() Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it. Your immediate response will be higly appreciated. Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean Dim cmdSQL As SqlClient.SqlCommand Dim blnReturn As Boolean = False Dim objParam As SqlClient.SqlParameter Dim arrData() As Byte Try If Not IO.File.Exists(strFileName) Then Throw New Exception("PDF file not found:" & strFileName) Else arrData = ReadFile(strFileName) End If cmdSQL = New SqlClient.SqlCommand("UPDATE [] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("", Me.Job.JobName), Me.m_cnnSQL) cmdSQL.CommandType = CommandType.Text objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1) objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image) objParam.Value = arrData objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat") cmdSQL.ExecuteNonQuery() cmdSQL.Dispose() blnReturn = True Catch ex As Exception Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly) Finally If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName) cmdSQL = Nothing End Try Return blnReturn End Function
First load up your query in query analyzer and check the execution plan to make sure it is not doing a table scan. If it is check to see if there is an index on [File_Name], if not then add one. If there is then try rebuilding the statistics on that table. Increasing the timeout value should be the approach of last resort.
-
First load up your query in query analyzer and check the execution plan to make sure it is not doing a table scan. If it is check to see if there is an index on [File_Name], if not then add one. If there is then try rebuilding the statistics on that table. Increasing the timeout value should be the approach of last resort.
-
The following function is used in my code to store PDF as BLOB in SQL Server database. When i am running my application, sometimes after the application is running for long time period and continusly updating database single field in SQL server containing millions of record it gives the following exception on cmdSQL.ExecuteNonQuery() Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I have checked the query it does not take much time to execute againts SQL Server so please dont give me suggestion of setting timeout property of command object. I need to know why this error comes after long execution time and how can resolve it. Your immediate response will be higly appreciated. Protected Overrides Function PutPDFFile(ByVal strFileName As String) As Boolean Dim cmdSQL As SqlClient.SqlCommand Dim blnReturn As Boolean = False Dim objParam As SqlClient.SqlParameter Dim arrData() As Byte Try If Not IO.File.Exists(strFileName) Then Throw New Exception("PDF file not found:" & strFileName) Else arrData = ReadFile(strFileName) End If cmdSQL = New SqlClient.SqlCommand("UPDATE [] SET [File_Name] = @PDFName, [File] = @PDFData WHERE [File_Name] = @DATName".Replace("", Me.Job.JobName), Me.m_cnnSQL) cmdSQL.CommandType = CommandType.Text objParam = cmdSQL.Parameters.Add("@PDFName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1) objParam = cmdSQL.Parameters.Add("@PDFData", SqlDbType.Image) objParam.Value = arrData objParam = cmdSQL.Parameters.Add("@DATName", SqlDbType.VarChar) objParam.Value = strFileName.Substring(strFileName.LastIndexOf(IO.Path.DirectorySeparatorChar) + 1).Replace("pdf", "dat") cmdSQL.ExecuteNonQuery() cmdSQL.Dispose() blnReturn = True Catch ex As Exception Me.m_objLog.Log(ex.Message, SOARLogType.sltLogOnly) Finally If IO.File.Exists(strFileName) Then IO.File.Delete(strFileName) cmdSQL = Nothing End Try Return blnReturn End Function
by default the SqlConnection object sets pooling = true which means there is a limited ammount of time that connection is allowed in the pool. Clicky[^] I have ran into the same situation where no matter how good my execution plan was or how normalized my data was I would still get time outs. After adding pooling = false to my connection strings the time outs stopped.. hope this helps Will
-
by default the SqlConnection object sets pooling = true which means there is a limited ammount of time that connection is allowed in the pool. Clicky[^] I have ran into the same situation where no matter how good my execution plan was or how normalized my data was I would still get time outs. After adding pooling = false to my connection strings the time outs stopped.. hope this helps Will
-
This is true, but there are two aspects of it, command timeout and connection timeout. Do you think they are related?
-
-
Agreed. But this is just not the execution plan and table statistics but sometimes the network traffic is high and the default CommandTimeout is just not sufficient. I have encountered this before.
It depends how high the timeout is and how much traffic you need to support. If you set your timeout too high, the entire site can be brought down by a few poorly performing pages. The timeout exists for a very important reason, to allow the server to recover when it is overloaded. I have encountered this a few times myself. IMHO. Exceution plan and table statistics are the most common reasons for an update to run slow especially when there are a number of records in the table and should be the first solutions considered since they have very little cost. Next, I'd evaluate contention with other queries by loading up the profiler and looking for long running operations.
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
-
Thanks guys for ur immediate response but i have tried the things u have pointed out. I ran the query through query analyzer and it seems to give me timeout their as well. I think this seems to be an issue with SQL server not with connection. My database only have 120000 records but i am storing PDF its size is over 10 GB. When i ran select query on a particular table it runs fine but when i try to update a row or record or delete a record it gives timeout using query analyzer. Any one who can point out this issue why is this happening. Once again thnx for ur immediate response. Jaffer
-
Thanks guys for ur immediate response but i have tried the things u have pointed out. I ran the query through query analyzer and it seems to give me timeout their as well. I think this seems to be an issue with SQL server not with connection. My database only have 120000 records but i am storing PDF its size is over 10 GB. When i ran select query on a particular table it runs fine but when i try to update a row or record or delete a record it gives timeout using query analyzer. Any one who can point out this issue why is this happening. Once again thnx for ur immediate response. Jaffer
-
Thanks guys for ur immediate response but i have tried the things u have pointed out. I ran the query through query analyzer and it seems to give me timeout their as well. I think this seems to be an issue with SQL server not with connection. My database only have 120000 records but i am storing PDF its size is over 10 GB. When i ran select query on a particular table it runs fine but when i try to update a row or record or delete a record it gives timeout using query analyzer. Any one who can point out this issue why is this happening. Once again thnx for ur immediate response. Jaffer
First of all the image data type can only hold 2GB of data so even if your operation didn't time out, it would fail. If you have to store documents which are that large, you are going to have to increase your command timeout. The connection timeout should only increase if the server is getting overloaded. 120,000 records is a relatively small amount for sql server to handle if indexes are setup properly. Make sure the indexes are on the table to support all your queries. If that doesn't fix the problem, then start looking to see if SQL server is bound up with disk or memory access from the large files, or if it is just load from the tables in the database.
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon