Resolve SQL Server Database Index Reorganization Page Level Locking Problem


By:   |   Updated: 2016-04-14   |   Comments (7)   |   Related: More > Maintenance

Problem

One of the most important aspects of SQL Server database maintenance is maintaining indexes. Over time indexes can become fragmented, so to fix fragmentation indexes can either be rebuilt or reorganized and this is usually dependent upon the level of fragmentation.  Index maintenance is usually a scheduled task and recently we ran into an issue where the job failed on one of the tables showing that the index can't be reorganized due to page lock issues. What is this page lock issue and why does it prevent us from reorganizing the index?

Solution

Assume we have a table named Product that requires index reorganization. If you check the index maintenance job history, the below error will be shown explaining that the reorganizing process can't complete due to page level lock issue.  Here is the actual error message:

The index "PK_Product" on table "Product" 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.

The index

Review Index Settings on Table

Letís query the problematic tableís indexes by querying the sys.indexes system table joined with the sys.objects table as follows:

Select Indx.name,
	Indx.type_desc,
	Indx.is_disabled,
	Indx.allow_page_locks,
	Indx .allow_row_locks 
from sys.indexes Indx
	left outer join sys.objects OBJ 
		on OBJ.object_id=Indx.Object_id
where OBJ.name ='Product'

You can see from the result below that the allow_page_locks and allow_row_locks options are disabled for that index.  The specific error we saw above was related to page level locking.

allow_page_locks and allow_row_locks options are disabled for the SQL Server index

What are Row Level and Page Level Locks

By default the allow-page-lock and allow-row-lock options are enabled, which allows SQL Server to choose between the three locking levels: row-level locks, page-level locks, and table-level locks. By disabling these options, you limit the type of locking that can occur on the object. 

The purpose of these different locking mechanisms is to limit how much of the table/index is locked at any one time, therefore allowing concurrent access to different parts of the table/index.

So, it is better to enable both allow-page-lock and allow-row-lock in order to let SQL Server decide the best locking option for each transaction.

Enable Row Level and Page Level Locks

In order to resolve the index reorganization issue, we will enable the row and page level locking by altering the index as shown below:

USE MSSQLTipsDemo 
GO
ALTER INDEX [PK_Product] ON [Production].[Product] SET ( ALLOW_PAGE_LOCKS = ON )
ALTER INDEX [PK_Product] ON [Production].[Product] SET ( ALLOW_ROW_LOCKS = ON )

We can also enable the row and page level locking using SSMS.  If you right click on the Index and select Properties and go to the Options page you can change the values to True as follows:

Row and page level locking from the Options tab of the Index Properties window using the SQL Server Management Studio

If we check the index properties again using the T-SQL code above, we will see the following result:

allow_page_locks and allow_row_locks options are enabled for the SQL Server index

Retry Index Maintenance After Change

If we run the index maintenance job again, it completes successfully as shown below:

Successfully completed SQL Server Maintenance Process
Next Steps


Last Updated: 2016-04-14


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Comments For This Article




Saturday, January 05, 2019 - 7:07:30 AM - Ahmad Yaseen Back To Top (78638)

†Hello Shaimakem,

Mnay thanks for your input here.

My recommendation for you is to†enable both allow-page-lock and allow-row-lock in order to let SQL Server decide the best locking option for each transaction.

Best Regardas,

Ahmad


Friday, January 04, 2019 - 2:32:35 PM - shaimakem Back To Top (78631)

Is there any implications in turning this setting to ON or OFF?

As I see several indexes in our tables that are already set to OFF when I used your query above.... (Very useful query thanks for posting it)..so not sure which one should be turned ON (depends if its used in a scheduled reindex\reOrg jobs?) as I don't want to wait for an error to occur.. or lock a page that can be used at the same time of the maint plans...etc.†


Thursday, March 15, 2018 - 8:18:27 AM - Ahmad Yaseen Back To Top (75425)

 Is there any other reorganizing process or job running at the same time? It may be locked by another task.

 

Best Regatds,

Ahmad

 


Thursday, March 15, 2018 - 6:41:49 AM - suresh Back To Top (75421)

 

 Executing the query "ALTER INDEX [AttributeID_idx] ON [dbo].[StatusMess..." 

failed with the following error: "The index "AttributeID_idx" (partition 1) on 

table "StatusMessageAttributes" cannot be reorganized because it is being

 reorganized by another process.". Possible failure reasons: Problems with the query,

 "ResultSet" property not set correctly, parameters not set correctly, or 

connection not established correctly.

 


Monday, August 01, 2016 - 8:10:49 AM - Ahmad Yaseen Back To Top (43017)

 Hi Suresh,

Please check the SQL Server errorlog or the server event viewer at the time of the error and provide me with the error.

 

Regards,

Ahmad

 


Saturday, July 30, 2016 - 11:00:45 AM - suresh Back To Top (43012)

Message

Executed as user: CHEC\svc_vrsqlwc15_cluadm. ... (Message 0)  Performing Index Maintenance on master... [SQLSTATE 01000] (Message 0)  Performing Index Maintenance on model... [SQLSTATE 01000] (Message 0)  ALTER INDEX [backupmediasetuuid] ON [msdb].[dbo].[backupmediaset] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0)  ALTER INDEX [backupmediafamilyuuid] ON [msdb].[dbo].[backupmediafamily] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0)  ALTER INDEX [nc1] ON [msdb].[dbo].[sysjobhistory] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [backupsetuuid] ON [msdb].[dbo].[backupset] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0)  ALTER INDEX [restorehistorybackupset] ON [msdb].[dbo].[restorehistory] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [clust] ON [msdb].[dbo].[sysjobactivity] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [pk_MSdbms_map] ON [msdb].[dbo].[MSdbms_map] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [pk_MSdbms_datatype_mapping] ON [msdb].[dbo].[MSdbms_datatype_mapping] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_syscollector_collection_items_internal] ON [msdb].[dbo].[syscollector_collection_items_internal] REBUILD [SQLSTATE 01000] (Message 0)  Performing Index Maintenance on msdb... [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_EventReceivers] ON [NW2010DB_Config1].[dbo].[EventReceivers] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_ContextDataResources] ON [NW2010DB_Config1].[dbo].[ContextDataResources] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_InstanceIDSequenceID] ON [NW2010DB_Config1].[dbo].[WorkflowProgress] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_Intiator] ON [NW2010DB_Config1].[dbo].[WorkflowInstance] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_WFInstanceID] ON [NW2010DB_Config1].[dbo].[WorkflowInstance] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_State] ON [NW2010DB_Config1].[dbo].[WorkflowInstance] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_63F77821] ON [NW2010DB_Config1].[dbo].[UserProfiles] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_Credential] ON [NW2010DB_Config1].[dbo].[WorkflowConstants] REORGANIZE [SQLSTATE 01000] (Message 0)  Performing Index Maintenance on NW2010DB_Config1... [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_HumanWorkflow] ON [NW2010DB_Projects].[dbo].[HumanWorkflow] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_WorkflowProgressIDTaskType] ON [NW2010DB_Projects].[dbo].[HumanWorkflow] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_HumanWorkflowID] ON [NW2010DB_Projects].[dbo].[HumanWorkflowApprovers] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IDX_HumanWorkflowApprovers_SPTaskID] ON [NW2010DB_Projects].[dbo].[HumanWorkflowApprovers] REORGANIZE [SQLSTATE 01000] (Message 0)  Performing Index Maintenance on NW2010DB_Projects... [SQLSTATE 01000] (Message 0)  Performing Index Maintenance on NW2010DB_Teams... [SQLSTATE 01000] (Message 0)  Performing Index Maintenance on NW2010DB_Tools... [SQLSTATE 01000] (Message 0)  ALTER INDEX [IX_MSSCrawlDeletedURL_AccessHash] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlDeletedURL] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0)  ALTER INDEX [IX_MSSCrawlDeletedURL_DocID] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlDeletedURL] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0)  ALTER INDEX [PK_MSSCrawlHostList] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlHostList] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IX_MSSCrawlHostList_Name] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlHostList] REORGANIZE [SQLSTATE 01000] (Message 0)  ALTER INDEX [IX_MSSCrawlQueue_Cluster] ON [Search_Service_Application_CrawlStoreDB...  The step failed.

 

How to analyze the root cause for this Index maintainance job failure?


Friday, April 15, 2016 - 8:27:30 AM - Alemayehu Back To Top (41237)

 Ahmad,

Very nicewell summarized  article. I had simmillar issue and maintenance jobs were failing because of page level locink was disabled. But what i did was very lengthy step i have to go to the error log to find exactly what happened and which index on which table then address one by one. Whereas here you provide us a short cut just running the TSQL statement will identify all the indexes and tables with page level locking disabled simillarly you can use the script to enable the page level locking. Very good article, simple  and relevent to the DBAs.

 

Many thanks

 



download


Recommended Reading

Update Statistics for All Tables and Databases in a SQL Server Instance

Reduce Time for SQL Server Index Rebuilds and Update Statistics

Move SQL Server Maintenance Plan from One Server to Another

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table





get free sql tips
agree to terms


Learn more about SQL Server tools