We have some guidance out on TechNet about the recommended settings for SQL Server for our various versions – so for example we recommend for 2010 at http://technet.microsoft.com/en-us/library/ee662107.aspx that you set AUTO_CLOSE to OFF, and AUTO_UPDATE_STATISTICS_ASYNC to ON. But we don’t always make a show of telling you what NOT to change. A good guide is to leave things at the default settings unless we recommend otherwise. For example, in 2013 we don’t recommend the UPDATE_STATISTICS settings because we, like SharePoint in 2010 and 2013, now have a timer job for that purpose.
One good example of this was a case I worked today with my colleague Vikram. The customer had an issue connecting from Project Professional to PWA – the error was “The following job failed to complete. Job Type: Load, Error ID: 42(0x2A), Error Description: An internal error occurred. My apologies if you are reading this having searched for that error – it has many causes and this one is probably way down the list of likely ones…It usually means it couldn’t get the enterprise global from the database.
In this case we could reproduce the issue with the customer’s database, and soon found the SQL error that was being thrown (The ‘More Info’ button in the dialog, the ULS logs and SQL Profiler helped here) – “Arithmetic overflow error converting numeric to data type numeric” as a result of executing the stored procedure MSP_WINPROJ_READ_ENTERPRISE_CUSTOM_FIELDS, which also has an set of GUIDs passed in that represent the custom fields of interest. However, all the data looked OK, and it even gave the same error with a NULL parameter indicating no custom fields. After trying a few things it was obvious from debugging the stored procedure that it had some issue with the eglobal version number – which should have been ‘14’ but was showing blank. The value in the database being queried was just fine - 14.1461140000, the same as on a working system I was comparing with. But it got me thinking – it turns 14.1461140000 into 14 – I wonder if some DB options would make that give an arithmetic overflow? Sure enough – the database had an option set to True for Numeric Round-Abort – so this implicit cast was being aborted.
I thought I’d also look to see how 2013 behaved with this wrong setting – and as I expected it also failed, but with a slightly different (but no more helpful) error on the client side (please click the link if you too see a message that you feel could be more helpful – I did…) – though to be fair the failure is slightly different just caused by the same root cause.
But at the back end it is very much more helpful! In the ULS logs I saw:
System.Data.SqlClient.SqlException (0x80131904): Error 1934, Level 16, State 1, Procedure MSP_UPDATE_USER_ACTIVITY, Line 14, Message: 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.
This is a great example of the improvements to logging we now have in 2013 to try and ensure that the log will give us the full answer – rather than just being a clue as to where we should go and look next.