Monday, August 2, 2010

SQL Server: The index "[IndexName]" on table "[TableName]" cannot be reorganized because page level locking is disabled.

http://www.interworks.com/blogs/bbickell/2010/05/10/sql-server-index-indexname-table-tablename-cannot-be-reorganized-because-p


SELECT *
FROM sys.indexes
WHERE ALLOW_PAGE_LOCKS = 0


SELECT 'ALTER INDEX [' + I.Name + '] ON [' + T.Name + '] SET (ALLOW_PAGE_LOCKS = ON)' As Command
FROM sys.indexes I
LEFT OUTER JOIN sys.tables T ON I.object_id = t.object_id
WHERE I.allow_page_locks = 0 AND T.Name IS NOT NULL

ex:ALTER INDEX [Refnum IDX] ON [Applicants] SET (ALLOW_PAGE_LOCKS = ON)

No comments:

Post a Comment