Many timeouts in error log
Problem reported by Aleksey Permyakov - August 3, 2015 at 5:15 AM
Submitted
Auto Purge Deleted Thread error detected
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.ApplicationBlocks.Data.SqlHelperInternal.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters)
   at SmarterTrack.Data.DataLayerShortcut.ExecuteNonQuery(IDbConnection& conn, String query, SqlParameter[] parameters)
   at SmarterTrack.HelperClasses.Threads.AutoPurgeDeletedThread.#Juh()
   at SmarterTrack.HelperClasses.Threads.AutoPurgeDeletedThread.#Iuh()
   at SmarterTrack.HelperClasses.Threads.AutoPurgeDeletedThread.DoWork()
 
Exception in the Ticket Watcher Thread Process
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   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.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at SmarterTrack.Data.EntityBase.GetListFromQueryInternal[T](IDbConnection& connection, String query, IDbDataParameter[] parameters)
   at SmarterTrack.Data.EntityBase.GetListFromQueryInternal[T](String query, IDbDataParameter[] parameters)
   at SmarterTrack.Data.TicketTimingEntity.GetMostRecentByTicketID(Int32 ticketID)
   at SmarterTrack.Data.TicketTimingEntity.SetCurrentStatus(IDbConnection& connection, TicketEntity ticket, Boolean CountInResponseTime, Boolean CountInInitialResponseTime)
   at SmarterTrack.Data.TicketEntity.Save(IDbConnection& connection, Int32 userDoingAction, InterfaceUsedTypes interfaceType, Dictionary`2 customFields, Boolean useCache)
   at SmarterTrack.Business.Tickets.TicketAssignments.AssignTicket(IDbConnection& connection, TicketEntity ticket, Int32 departmentId, Int32 groupId, Int32 userId, Int32 userIdDoingAction, InterfaceUsedTypes interfaceType)
   at SmarterTrack.Business.Tickets.TicketAssignments.AssignTicket(TicketEntity ticket, Int32 departmentId, Int32 groupId, Int32 userId, Int32 userIdDoingAction, InterfaceUsedTypes interfaceType)
   at SmarterTrack.HelperClasses.Threads.Processes.TicketWatcherProcess.#1wb()
   at SmarterTrack.HelperClasses.Threads.Processes.TicketWatcherProcess.DoWork()
 
And similar. Is there something wrong with SQL Server?
 

4 Replies

Reply to Thread
0
User Replied
Hello,
Hmm... the stack trace is a little odd. Seems to be trying to assign a ticket but gets stuck in ticket timing. Something may be wrong with the database (such as the query is hitting an exception due to a duplicate key error or something) or connecting to the database. Does this happen all the time? or only when you transfer a ticket? What version of SmarterTrack are you on?
0
Aleksey Permyakov Replied
It happens always. More often exceptions are caused by "delete thread" and ticket imporing.
In the "Recent Expensive Queries" I can see that ST have problem inserting search tokens:
insert into [dbo].[st_SearchTokenStats]
(SearchTokenID, SearchFieldID, DateCalculatedUTC, WeightFactor)
(
select R.SearchTokenID, R.SearchFieldID, @dt, case when count(R.TicketID) > 448448 then 0.001 when count(R.TicketID) > 336336 then 0.01 when count(R.TicketID) > 224224 then 0.04 when count(R.TicketID) > 168168 then 0.1 when count(R.TicketID) > 112112 then 0.3 when count(R.TicketID) > 56056 then 0.4 when count(R.TicketID) > 44844 then 0.5 when count(R.TicketID) > 33633 then 0.7 when count(R.TicketID) > 22422 then 1.0 when count(R.TicketID) > 11211 then 1.5 when count(R.TicketID) > 5605 then 2.0 when count(R.TicketID) > 2802 then 4.0 when count(R.TicketID) > 1121 then 6.0 when count(R.TicketID) > 224 then 10.0 else 15.0 end from [dbo].[st_SearchTokensInTickets] R
left join [dbo].[st_SearchTokenStats] S on R.SearchTokenID = S.SearchTokenID and R.SearchFieldID = S.SearchFieldID
where R.SearchTokenID > 0 and (S.SearchTokenID is null or S.SearchFieldID is null)
group by R.SearchTokenID, R.SearchFieldID
)
This query takes TOO long to execure. I have now 2 092 235 records in SearchTokenStats table.

SQL Server 2012R2 STD x64
Version 8.6
0
User Replied
I am not sure what can be causing this. It could be an IIS setting or a DataBase error. I recommend on contacting support so that they can take a closer look at your database. Is your database large? If you dont mind your searching not working for a bit you could try a re-index to help clean up the search tokens (but its most likely not going to help with the assigning/deleteing tickets issue). https://portal.smartertools.com/kb/a2590/reindex-smartertrack-search-tables.aspx
0
Aleksey Permyakov Replied
Thanx in advice.
I have done reindexing. At the bigginning everything was fine, now timeout are returning again.
I believe, the problem is in _SearchTokensInTickets. It contains more than 80 000 000 records. Is that OK?

Reply to Thread