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.

SQL Server : Msg 547, Level 16, State 0, Procedure sp_delete_job

SQL Server : Deleting job for a deleted maintenance plan.

On certain occasions I have seen that deleting a maintenance plan is not sufficient and it leaves behind the job that was created as part of the maintenance plan. If you tried to delete the job after deleting the maintenance plan then you will get the following message.





Msg 547, Level 16, State 0, Procedure sp_delete_job, Line 178
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id".
The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated.

Looks like there are some remnants of the maintenance plan that did not get deleted from some system views in the MSDB database.

To fix this take the following steps.

1) Get the name of the job
2) Run the following command

        Use msdb

        delete FROM sysmaintplan_subplans AS subplans INNER JOIN
        sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id INNER JOIN
        sysmaintplan_log ON subplans.subplan_id = sysmaintplan_log.subplan_id
        WHERE (syjobs.name = @job_name)

3) Then run the following command

        Use msdb

        delete FROM sysmaintplan_subplans AS subplans INNER JOIN
        sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
        WHERE (syjobs.name = @job_name)

Make sure you replace @job_name with the actual job name.