SharePoint 2010–After Update “Could not find stored procedure ‘proc_UpdateStatisticsNVP”

Oh Microsoft, how I do love to hate you… so per KB2635071 Microsoft has released a fast publish which basically says “Nothing to see here, move along.”

Or, to put it more exactly…

This is a known issue that will be fixed in a future release of the product. The error does not affect the functionality of the product in any way and can be safely ignored.

However, there are those of us who actually like to have a nice and clean event log, if for no other reason than when we filter for Critical and Error events we actually see issues that are pertinent to troubleshooting our farms, there needs to be a fix so we don’t end up training our support teams “Ignore SharePoint event alerts, it always does that.”  We’ve sort of missed the forest for the trees here.  The bigger picture should be how do we separate the real alerts from the ones we can safely ignore in a way that a support person can easily recognize.  In this case, the solution is to just add the proc_UpdateStatisticsNVP to the other databases so our SharePoint farm doesn’t alert on the issue and our event log stays cleaner and hopefully only shows relevant Critical and Error events.

Issue:
Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 15/07/2011 12:00:31 AM
Event ID: 5586
Task Category: Database
Level: Error
Keywords:
User: ray
Computer: SPS2010
Description:
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.
Could not find stored procedure ‘proc_UpdateStatisticsNVP’.

<Date and Time>T00:00:13.143: Starting
<Date and Time>T00:00:13.143: Dropping automatically created stats on user tables
<Date and Time>T00:00:13.143: Updating statistics on user indices
<Date and Time>T00:00:13.150: Statistics for dbo.IX_Dependencies_ObjectId are now being updated with default sampling.
<Date and Time>T00:00:13.150: Rebuilding
<Date and Time>T00:00:13.153: Done
<Date and Time>T00:00:13.153: Statistics for dbo.IX_Objects_Version are now being updated with default sampling.

Cause:
The SharePoint 2010 Health Analyzer consists of a rule with the Title "Databases used by SharePoint have outdated index statistics". This health rule runs on a daily schedule and checks the SharePoint databases for outdated index statistics. If databases index statistics are found to be outdated, the rule automatically attempts to update the statistics on the databases. The error is generated because the health rule attempts to run ‘proc_UpdateStatisticsNVP’ stored procedure against the configuration database, however, this stored procedure does not exist in the configuration database.

A quick search through \Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\SQL showed [dbo].[proc_UpdateStatistics] was calling this proc.
So I assume wherever I find [dbo].[proc_UpdateStatistics] I should also find [dbo].[proc_UpdateStatisticsNVP]

Fix:
The fix requires that each DB that has [dbo].[proc_UpdateStatistics] also have [dbo].[proc_UpdateStatisticsNVP].  The following SQL Query will help you identify the DB’s:

exec sp_msforeachdb ‘if exists(select name from [?].sys.objects where type = ”P” and name like ”%proc_UpdateStatistics%”) AND not exists(select name from [?].sys.objects where type = ”P” and name like ”%proc_UpdateStatisticsNVP%”) select ”?”’

This will give you the DB’s that have [dbo].[proc_UpdateStatistics] but don’t have [dbo].[proc_UpdateStatisticsNVP] then all you need to do is create the proc using an existing one.  You can do this by finding the proc_UpdateStatisticsNVP, scripting it as a Create to the new query editor window and then running the create script against each database that is identified from the script above.

Now, supposedly this will have no effect on the performance of the farm, except of course KEEP IT FROM LOGGING IRRELEVANT ERRORS.  Long term this means our support people will hopefully actually trust SharePoint event logs and pay attention when an Error or Critical is actually logged.

Leave a Reply