Msg 2552 Level 16 State 2 Line 1

The index "PK_EmployeedID" (partition 1) on table "Employees" cannot be reorganized because page level locking is disabled. 

If you try to reorganize an index using a command like the following

USE [AdventureWorksDW];ALTER INDEX [PK_DimAccount] ON [dbo].[DimAccount] REORGANIZE  

And you get the above message then it means that you have disabled PAGE LEVEL LOCKING.
Page level locking when enabled helps in locking each page so that way the lock does not have to escalate from a row to a table.

But this will indeed prevent you from reorganizing the index since that is an online operation and you need to move pages.

There are two solutions for this.

1) Enable page level locking: But this might have serious performance repercussions. Until now I haven't seen any particular disadvantage in having page level locking enabled but be careful when you enable that.
2) Rebuild the index: If the first option is not possible then rebuild the index instead of reorganizing it.

Last update stats time

Ever wondered when was the last time the statistics got updated. If yes, then use the following query against the database and find that last time the stats got updated.


select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U' and b.name !=  a.name order by stats_last_updated_time desc

If it's been a while since the statistics got updated then please update the statistics.

Maintenance cleanup task not deleting older backup files.

SQL Server 2005/2008 : Maintenance cleanup task not deleting older backup files.

On several occasions I have heard people mention that the maintenance cleanup task to delete older backup files from the specified directory.

In the maintenance cleanup task you can mention the directory where you want backup files to be deleted if older than x days. You can also mention 'To include subfolders' to be searched for old files. But in spite of this it won't delete the files.

The problem could be a typo. Make sure you have the correct path. I normally go to the folder in Windows explorer and copy the path.

But one of the most difficult typos to detect is the file extension. I have seen several people mention the file extension or the backup (either BAK or TRN) with a DOT '.'

Like '.BAK' or '.TRN' and this causes the problem. So.....

Do not include the DOT '.' in the file extension name.

Msg 547 Level 16 State 0 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.