First published on MSDN on May 23, 2014
A customer recently called me because they were having issues with site creation in their test farm. They couldn't sites in any web app and content DB. To their knowledge, nothing in the environment had changed recently. They had recently installed SP1 for SharePoint 2013, but believed they had created new sites after the error free install. I took a look at their ULS logs, and noticed a bunch of blocking, as well as the follow ULS messages.
Event log message was: 'Failed to create the 'Cache Profiles' list.'. Exception was: 'Microsoft.SharePoint.SPException: Cannot remove file "Item". Error Code: 6404. ---> System.Runtime.InteropServices.COMException: <nativehr>0x81070207</nativehr><nativestack></nativestack>Cannot remove file "Item". Error Code: 6404.
at Microsoft.SharePoint.Library.SPRequestInternalClass.AddOrDeleteUrl(String bstrUrl, String bstrDirName, Boolean bAdd, UInt32 dwDeleteOp, Int32 iUserId, Guid& pgDeleteTransactionId)
at Microsoft.SharePoint.Library.SPRequest.AddOrDeleteUrl(String bstrUrl, String bstrDirName, Boolean bAdd, UInt32 dwDeleteOp, Int32 iUserId, Guid& pgDeleteTransactionId) -
-- End of inner exception stack trace ---
at Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx)
at Microsoft.SharePoint.Library.SPRequest.AddOrDeleteUrl(String bstrUrl, String bstrDirName, Boolean bAdd, UInt32 dwDeleteOp, Int32 iUserId, Guid& pgDeleteTransactionId)
at Microsoft.SharePoint.SPFolder.DeleteCore(DeleteOp deleteOp)
at Microsoft.SharePoint.SPContentType.DeleteFolder()
at Microsoft.SharePoint.SPContentTypeCollection.Delete(SPContentTypeId id)
at Microsoft.SharePoint.Publishing.Internal.ProvisioningHelper.ReplaceAllContentTypesOnList(SPList list, SPContentType contentType)
at Microsoft.SharePoint.Publishing.Internal.RootProvisioner.CreateCacheProfilesList()'
System.Data.SqlClient.SqlException (0x80131904): Cannot use the PAGE granularity hint on the table "dbo.AllLinks" because locking at the specified granularity is inhibited.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command) ClientConnectionId:c77f3629-9999-413b-bab4-7d547263fcac
The SQL exception seemed very suspicious and seemed to possibly correlate to all the blocking. I couldn't understand why locking at the page level wasn't allowed. In order for SQL to perform optimally, it uses locks. We took a look at the lock settings on the indexes for AllLinks table. Both "Allow row locks" and "Allow page locks" were set to false.
At this point, I suggested we create a new content database and try creating a site collection there. Site creation did work in the new content DB. We compared the values for the locking, and in the new content DB (and my lab) they were set to true. Somewhere along the line, the values in the existing content DBs for the indexes on this table got set to false. My initial thought was that some sort of maintenance may have done this. We took a look at the maintenace plan, and the only thing that was happening was backups. It's a bit of a mystery as to why these values were changed. I couldn't find any support cases where this had previously been reported.
Now, I know what you're all thinking. Ron, all you need to do is to change those values back and viola. Well, the problem with this is, it's UNSUPPORTED to make that kind of change to a SharePoint database. We don't know what changed the value, and what else may be broken. I'm going to suggest that if possible, restore problematic DBs from backup AND/OR call Premier support for assistance.
Supported DB Changes for SP DBs
http://support.microsoft.com/kb/841057
Additional background: In this instance, the farm was patched with the original release of SP1, followed by the fixed version of SP1. We only saw this symptom in 1 or 2 environments.