Wednesday, April 4, 2012

EventID 5586 SharePoint Foundation

 

http://seth.killey.me/?p=462
Unknown SQL Exception 2812 occurred. Additional error information from SQL Server is included below.
Could not find stored procedure ‘proc_UpdateStatisticsNVP’.
The reason for this is there is a stored procedure called proc_UpdateStatistics which references proc_UpdateStatisticsNVP, however SP1 fails to create the later stored procedure in all databases.  This website provides a nice script to identify all databases missing the stored procedure.  Just run the following:
  • 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 ”?”’
Now that you know which databases are missing this stored procedure, use the link below to download a script to create the stored procedure.  Just make sure to select the appropriate database from the drop down in SQL or type USE [databasename] GO at the beginning of the script
CREATE_proc_UpdateStatisticsNVP

Could not find stored procedure 'proc_UpdateStatisticsNVP' June CU 2011
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'.
Cause:
After the June 2011 CU I started getting this error every night in the logs.
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 where ever 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...
 
  • Turn off the Usage and Data Collection by navigating to your Central Administration > Monitoring > Configure usage and health data collection.  Uncheck both Enable usage data collection and Enable health data colletion, assuming they are checked, and click OK.
Once this is done restart the SharePoint 2010 Timer service.  Then re-enable Usage and Data Collection by checking those same items again.  The error should stop appearing in the logs now.

No comments:

Post a Comment