0

I have one issue at my work. It about SQL Server stuck one table during has many many transaction request from branches. My solution everyday is cannot do anything without restart SQL Server service.

Below is error log:

E60D17110910021661V1.9.8.3 - 2017-11-09 10:16 :  Error: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.\ Details:    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at TCAM_SOLUTION.Forms.General.CustomerNew.RepositoryBase.ExecuteReader(SqlCommand cmd)
   at TCAM_SOLUTION.Forms.Service.SaleOrdering.ServiceInvoices.ServiceInvoiceRepository.GetExistedRepairOrderDetails(List`1 repairOrderDetailIds)
   at TCAM_SOLUTION.Forms.Service.SaleOrdering.ServiceInvoices.ServiceInvoiceService.HasItemsSaved(ServiceInvoice invoice)
   at TCAM_SOLUTION.Forms.Service.SaleOrdering.ServiceInvoices.FormServiceInvoice.IsNotValidated()
   at TCAM_SOLUTION.Forms.Service.SaleOrdering.ServiceInvoices.FormServiceInvoice.btnSave_Click(Object sender, EventArgs e)
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Many thanks in advance.

Suy

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Uy Suy
  • 11
  • 6
  • Sounds like some transaction has a hold on a table. And all other transactions timeout while waiting. What is this failing transaction trying to do to that table? And which other transactions touch that same table? Take a look at https://stackoverflow.com/questions/8749426/how-to-find-what-is-locking-my-tables – donPablo Aug 22 '18 at 05:06
  • I use store procedure in sql server and some logic in that more complex. – Uy Suy Aug 22 '18 at 06:05
  • Long-term blocking is often a symptom that query and index tuning is needed. Check the execution plans of the queries to read and write to the problem table. – Dan Guzman Aug 22 '18 at 11:12
  • my store procedure working multi task and I think may be many users request then that one of store procedure working repeated that's why it stuck only that table. For stuck it occur only 1 table is Sale table. – Uy Suy Aug 23 '18 at 02:42

0 Answers0