UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT
Hi,
Our FIM portal has been working fine, but as of a few days ago accessing a lot of areas of the site returns the error: "There's an error in the Resource Control. Please contact your help desk or system administrator".
I've tried iisreset and restarting the FIM Service.
I enabled tracing and found a lot of errors like the following:
Microsoft.ResourceManagement: Microsoft.ResourceManagement.WebServices.Exceptions.UnwillingToPerformException: Other ---> System.Data.SqlClient.SqlException: Reraised Error 50000, Level 16, State 1, Procedure RetrieveWorkflowDataForHostActivator, Line
208, Message: <_x0040_failedTasks Task="A failure occurred while taking action on the Requests contained in the RequestMonitor table." FailureInfo="UPDATE failed because the following SET options have incorrect settings: 'NUMERIC_ROUNDABORT'.
Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations."/>
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.NextResult()
at Microsoft.ResourceManagement.Data.DataAccess.RetrieveWorkflowDataForHostActivator(Int16 serviceId, Int16 pingIntervalSecs, Int32 activeHostedWorkflowDefinitionsSequenceNumber, Int16 workflowControlMessagesMaxPerMinute, Int16 requestRecoveryMaxPerMinute,
Int16 requestCleanupMaxPerMinute, Boolean& doPolicyApplicationDispatch, ReadOnlyCollection`1& activeHostedWorkflowDefinitions, ReadOnlyCollection`1& workflowControlMessages, List`1& requestsToRedispatch)
--- End of inner exception stack trace ---
at Microsoft.ResourceManagement.Utilities.ExceptionManager.ThrowException(Exception exception)
at Microsoft.ResourceManagement.Data.Exception.DataAccessExceptionManager.ThrowException(SqlException innerException)
at Microsoft.ResourceManagement.Data.DataAccess.RetrieveWorkflowDataForHostActivator(Int16 serviceId, Int16 pingIntervalSecs, Int32 activeHostedWorkflowDefinitionsSequenceNumber, Int16 workflowControlMessagesMaxPerMinute, Int16 requestRecoveryMaxPerMinute,
Int16 requestCleanupMaxPerMinute, Boolean& doPolicyApplicationDispatch, ReadOnlyCollection`1& activeHostedWorkflowDefinitions, ReadOnlyCollection`1& workflowControlMessages, List`1& requestsToRedispatch)
at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.RetrieveWorkflowDataForHostActivator()
at Microsoft.ResourceManagement.Workflow.Hosting.HostActivator.UpdateServiceHosts(Object source, ElapsedEventArgs e)
If anyone has ideas on what troubleshooting steps I could take, I appreciate it.
Many thanks,
Sami
December 8th, 2010 11:19am
Sami
I think you need to reindex the FIM databases
You can use this statement on your SQL Server:
USE <database>
EXEC sp_MSforeachtable
@command1 = « print ‘?’ DBCC DBREINDEX(‘?’, ‘ ‘, 80) »
GO
EXEC sp_updatestats
GO
Database is FIMService and FIMSynchronizationService, you can found more information into the following threads
http://social.technet.microsoft.com/Forums/en-US/ilm2/thread/d7761296-6ed2-4c43-80b7-547cf4c897fb
http://blogs.msdn.com/b/darrylru/archive/2010/02/02/extending-fim-timeouts.aspx
hope this helps
Fabrice
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2010 12:16pm
Hi Fabrice,
Thank you! I will see if the database team can do that. It's a production server and they wisely keep it pretty locked down.
I appreciate the help!
Sami
December 9th, 2010 11:28am
No pb
If it's a production server and this resolve your problem, I strongly recommend you to create a SQL Job to do a reidexing of both FIM databases often.
Where often depends of the number of write/read the SQL DB perform, once a week is a good starting point
But you can discuss that with your DB team ;)
Fabricehttp://www.iamthefrenchblog.com/
Free Windows Admin Tool Kit Click here and download it now
December 9th, 2010 11:54am
Good idea. I'll pass it along. Thanks again!
December 10th, 2010 11:15am