Error: Cannot use the ROW granularity hint on the table because locking at the specified granularity is inhibited

I recently encountered this error message on our SQL server 2008:

Error:

Cannot use the ROW granularity hint on the table “xxx” because locking at the specified granularity is inhibited

Cause:

This is usually caused by indexes created on a database table where ALLOW_PAGE_LOCKS is set to OFF. This behaviour means that all access to the index will be via a table lock, not a row lock.

Run following TSQL command against the affected database table:

[sourcecode language=”sql”]

SELECT name indexname

FROM sys.indexes

WHERE object_id = object_id(‘XYZTable’) AND

allow_row_locks = 0 AND allow_page_locks = 0

[/sourcecode]

Fix:

– Change the ALLOW_PAGE_LOCKS value to ON (Enabled): Row locks rather than table locks

 
comments powered by Disqus