SQL Server 2005/2008 ; Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

SQL Server 2005/2008


Failed:(-1073548784) Executing the query "UPDATE STATISTICS [dbo].[v_errorlog]
WITH SAMPLE 50 PERCENT,NORECOMPUTE

Cannot create or update statistics on view because both FULLSCAN and NORECOMPUTE options are required

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The problem is due to the fact that the maintenance plan was trying to update the stats on views too. And on a view we can not update the stats with anything other than full scan.
The maintenance plan was configured to run update stats on all the databases. But when you do that, you do not get the choice to pick if you want to exclude or include tables/views or specific tables/views.

It pretty much runs the plan against all DBs, all tables and all views. You can exclude the views only if you select one database at a time. If you have limited number of databases in the instance then create a maintenance plan like the following.

1)       Create two ‘Update statistics’ tasks for each database.
2)       The first task for each database will update the statistics only on tables. You can choose the  
           sampling percentage in this step.
          
3)       The second task  for each database will update the statistics only on views. But make sure you 
           pick full scan option in this step.
4)       Repeat step 2 and 3 for each database.

If it is not possible to create multiple tasks due to the large of databases in the instance, then you will have to write a script to this. If you need help then let us know.