ServiceDesk

Hi again,

Not sure how or why, but now every time any of the technicians open an incident to view, or any other process for that matter, it hangs for quite a while, and the error log shows the following:

Application Name : ProcessManager
Process ID : 5732
Date :4/24/2013 7:05:30 AM
Log Level :Error
Log Category :ProfileService
Machine Name : VM-MRL3SEPRASD2
Message :
[global] Error getting ProfileFieldValuesByReferenceID, ReferenceID=6848c878-accd-11e2-b2be-005056a639fe
[global] Client Host Information:
[global] IP: 10.84.16.31
[global] HostName: 10.84.16.31
[global] Browser: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E)
[global] -- error.ToString() --
[global] System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
[global]    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
[global]    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
[global]    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
[global]    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
[global]    at System.Data.SqlClient.SqlDataReader.get_MetaData()
[global]    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
[global]    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
[global]    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
[global]    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
[global]    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
[global]    at System.Data.SqlClient.SqlCommand.ExecuteReader()
[global]    at WebServiceIMSourceData.Search(WhereCondition[] whereConditions)
[global]    --- End of inner exception stack trace ---
[global]    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
[global]    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
[global]    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
[global]    at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
[global]    at LogicBase.Ensemble.Profile.ProfileService.GetProfileKeyValuePairs(String sessionID, String referenceID, String referenceTypeID, String profileDefinitionID)
 

The process view page will eventually open, but it takes a good 30-45 seconds+ to do so. 

Any ideas on how I can rectify this? 

Thank you all for the continued support.

Reboot of the SQL server seems to have resolved the issues for now...

155 days without a reboot for a Windows server is not recommended apparently. 

EDIT - looks like the issue is back.  Any assistance is appreciated.

It is clearly a SQL Timeout - the calls from ServiceDesk to database are simply timing out on the SQL side. Check if this is happening only with ProfileFieldValuesByReferenceID or with various things.

If this is happening with pretty much everything, the best place to start is looking at the SQL Server performance/configuration/maintenance.

7.5 is much better at playing with others than it was in 7.1 but it still may not tolerate a crowded SQL server very well when SQL and/or ServiceDesk usage is high.

Thanks Toomas.

I actually had to call Support on this one.  We tracked it down to a custom data table. 

I followed Jason Short's video series on adding custom data (to the letter), and the table that was created to hold the custom data had a lot of NULL values, as every time the Technician Incident Form is loaded, it gets populated with a NULL SessionID.  The SessionID is only populated if an incident is created, so the NULLs stay in the table.

Because of this, apparently, it was taking an incredibly long time to slog through all of table rows (there were only about 200 rows in it).  Once I removed the custom data from the Process View Page, response was much better.

So now I have to capture this data a little differently in order to be able to report on it.  Not as elegant a solution, but it's working.

Before reworking everything you have.. Create a SQL job to fire off and delete all entries where the SessionID is null and see if that resolves the issue. 

Unfortunately, when the Technician Form is loaded, an entry with a null sessionid is created.

I also tried cleaning the null values out manually, but the query ran for over an hour before timing out.  Something about it just didn't work.  So now I handle it through an HTML merge of the data into the Incident Description.  Like I said, it works, but it's just not as pretty.