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

Maintenace Plans are one of the greatest things ever.  They save SOOOO much time and effort!

Over the weekend, I finally found some time to research a problem I was hitting with Maintenance Plans on a new server

Failed:(-1073548784) Executing the query “ALTER INDEX [DSPFSC_INDEX02] ON [dbo].[DSPFSC] REO…” failed with the following error: “The index “DSPFSC_INDEX02” (partition 1) on table “DSPFSC” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Over the years, I’ve seen a LOT of developers who create indexes without standard settings for ALLOWPAGELOCKS and ALLOWROWLOCKS.

This can cause problems, especially when you run ‘Maintenance Plans’ on the database. I just HAD to send a quick shout out on how to fix this.

1) Run this SQL ‘template’ for the database that you need to standardize.

2) Copy the output from the Results Window back into a ‘New Query’ Window.

3) Change the database context to the correct database. Execute the results from Step 1.

SET NOCOUNT ON
select
‘PRINT ‘ + CHAR(39) + ‘Standardizing Locks for table ‘ + name + CHAR(39) + CHAR(10) + ‘GO’ + CHAR(10)
+
‘EXEC SP_INDEXOPTION ‘ + CHAR(39) + name + CHAR(39) + ‘, ‘ + CHAR(39) + ‘AllowRowLocks’ + CHAR(39) + ‘, True ‘ + CHAR(10) + ‘GO’ + CHAR(10)
+
‘EXEC SP_INDEXOPTION ‘ + CHAR(39) + name + CHAR(39) + ‘, ‘ + CHAR(39) + ‘AllowPageLocks’ + CHAR(39) + ‘, True ‘ + CHAR(10) + ‘GO’ + CHAR(10)
from sysobjects
where xtype = ‘u’

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.