Unexpected error when changing the Top Navigation

When I had all the problems with our Replicator, and after it was finally solved, it started replicating the map and navigation structure. I desperately tried to stop the replication because I could see that it was altering my Top Navigation which I don’t wanted. But the damage was already done, and I went into the Site Actions and Navigation and thought I just change it back. Nooooo. After replication had changed it and I tried to stop it something must have happened because I got this error message now:

“An unexpected error occured while manipulating the navigational structure of this Web”

So I could no longer change the Navigation, and all I wanted to do was to reorder it under “Global Navigation”. Another important thing, I could change the navigation on subsites! But not on the top site in my site collection.

Googled the error and found that this had been resolved with April CU 2013. Upgraded to that, but it did NOT resolve my problem.

Went through the ULS logs and it reported:

“System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object ‘dbo.NavNodes’ with unique index ‘NavNodes_AltPK’. The duplicate key value is (25a584d5-0f03-4bb5-bdea-2e57030c7817, 4e340cbd-639b-4d6e-aa19-cdb73ac35a00, 1002, -7).  The statement has been terminated.     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.ConsumeMetaData()     at System.Data.SqlClient.SqlDataReader.get_MetaData()     at System.Dat… 852bae04-377b-43d5-b8f5-f354a1eb54b2
10/01/2013 13:55:02.05* w3wp.exe (0x2C34)                        0x07A0 SharePoint Foundation          Database                       d0d6 High     …a.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.ExecuteReader(CommandBehavior behavior)     at Microsoft.SharePoint.Utilities.SqlSe… 852bae04-377b-43d5-b8f5-f354a1eb54b2
10/01/2013 13:55:02.05* w3wp.exe (0x2C34)                        0x07A0 SharePoint Foundation          Database                       d0d6 High     …ssion.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQueryInternal(Boolean retryfordeadlock)     at Microsoft.SharePoint.SPSqlClient.ExecuteQuery(Boolean retryfordeadlock) 852bae04-377b-43d5-b8f5-f354a1eb54b2
10/01/2013 13:55:02.05  w3wp.exe (0x2C34)                        0x07A0 SharePoint Foundation          Database                       8z23 Unexpected Unexpected query execution failure in navigation query, HResult -2146232060. Query text (if available): “BEGIN TRAN DECLARE @abort int SET @abort = 0 DECLARE @EidBase int,@EidHome int SET @EidBase = 0 SET @EidHome = NULL IF @abort = 0 BEGIN EXEC @abort = proc_NavStructAllocateEidBlockWebId @wssp0, @wssp1, @wssp2, @EidBase OUTPUT SELECT @wssp3 = @EidBase, @wssp4 = @abort END IF @abort = 0 BEGIN EXEC @abort = proc_NavStructMoveNode ’25A584D5-0F03-4BB5-BDEA-2E57030C7817′,’4E340CBD-639B-4D6E-AA19-CDB73AC35A00′,4945,1002,-2,N’20130928 16:17:25′,@EidBase,@EidHome, @wssp5 OUTPUT SELECT @wssp6 = @abort END IF @abort = 0 BEGIN EXEC proc_NavStructLogChangesAndUpdateSiteChangedTime @wssp7, @wssp8, NULL END IF @abort <> 0 BEGIN ROLLBACK TRAN END ELSE BEGIN COMMIT TRAN END IF @abort = 0  BEGIN EXEC pro… 852bae04-377b-43d5-b8f5-f354a1eb54b2
10/01/2013 13:55:02.05* w3wp.exe (0x2C34)                        0x07A0 SharePoint Foundation          Database                       8z23 Unexpected …c_UpdateDiskUsed ’25A584D5-0F03-4BB5-BDEA-2E57030C7817′ END ” 852bae04-377b-43d5-b8f5-f354a1eb54b2
10/01/2013 13:55:02.05  w3wp.exe (0x2C34)                        0x07A0 SharePoint Foundation          General                        8kh7 High     An unexpected error occured while manipulating the navigational structure of this Web. 852bae04-377b-43d5-b8f5-f354a1eb54b2″

So values that the top navigation is using, are duplicated in a table called “dbo.NavNodes” in the content db. Or are invalid somehow.
I opened the dbo.NavNodes db and copied all lines in it, to find that duplicate key “(25a584d5-0f03-4bb5-bdea-2e57030c7817, 4e340cbd-639b-4d6e-aa19-cdb73ac35a00, 1002, -7)” and it was just a page on the site, checked in and published. Cannot really see how this page would mess all up, it has been there for quite a long time also.

I found an article that suggested to run a SQL query on the content db, to find the duplicates:

SELECTTOP(20)Count(nav.Eid)AS ‘DuplicateCount’, nav.DocId, ad.Dirname, ad.Leafname FROM NavNodesAS nav with(nolock)INNER JOIN AllDocsAS ad with(nolock)ON nav.Docid = ad.Id WHERE nav.EidParent= 1025 AND DocID IS NOT NULLGROUP BY nav.DocId, ad.DirName, ad.LeafNameORDER BY ‘DuplicateCount’ DESC

And the result was:

So I knew that some nodes were corrupt, and finally I found a useful article that really helped solving my problem. I ran this command a few times and carefully testing the Navigation between each, (go to Site Actions and Navigation and try to change the order in the Global Navigation). I had to run the script that deletes the nodes a couple of times (three):

Open PowerShell on the server, check the size of those objects at the top level:

$siteUrl = “http://server“
$spWeb = Get-SPWeb $siteUrl
$spWeb.Navigation.QuickLaunch | ft Title
$spWeb.Navigation.TopNavigationBar | ft Title
 
Then delete the QuickLaunch Nodes using this cmd:

$siteUrl = “http://server“
$spWeb = Get-SPWeb $siteUrl
    Foreach($node in ($spWeb.Navigation.QuickLaunch))
{
    write-host “The node title is “$node.Title
    $node.Delete()
}
$spWeb.Dispose()

Then delete the TopNavigationBar Nodes:

$siteUrl = “http://server“
$spWeb = Get-SPWeb $siteUrl
    Foreach($node in ($spWeb.Navigation.TopNavigationBar))
{
    write-host “The node title is “$node.Title
    $node.Delete()
}
$spWeb.Dispose()

First when you run these commands you will get an error message: “Cannot complete this action” and just continue until that goes away:

You know when you should stop because then you don’t get a red error message any longer:

Now I went into the Site Actions > Navigation again on my site collection and now I could change the order and save.