SQL Server重组所有表中的索引 [英] SQL Server Reorganizing indexes in all tables

问题描述

我一直在使用此过程重新组织数据库的所有表中的索引：

I am have been using this procedure to reorganize the indexes in all my tables for a database:

EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"
GO

我遇到了这个问题，执行此操作时出错：



重新组织数据库中每个表的索引。

重新组织[dbo]的索引。[ASPStateTempApplications]

重新组织[dbo]的索引。[tposCompanySettings]

重新组织[dbo]的索引。[tpaUnits]

重组[dbo]的索引。[tpsCrewRecurrence]

重组[dbo]的索引。[tplIssueTypes]

重组[dbo]的索引。[tpwLocationGroups]

消息1943，级别16，状态1，行1

无法重新组织表tpwLocationGroups上的索引IDX_tpwLocationGroups_CID，因为禁用了页级锁定。



有谁知道如何修改脚本（或不同的脚本）以跳过无法执行此操作的索引并继续到下一个表格？



谢谢

I ran into this issue where there was an error performing this operation:

Reorganizing indexes on every table in the database.
Reorganizing indexes for [dbo].[ASPStateTempApplications]
Reorganizing indexes for [dbo].[tposCompanySettings]
Reorganizing indexes for [dbo].[tpaUnits]
Reorganizing indexes for [dbo].[tpsCrewRecurrence]
Reorganizing indexes for [dbo].[tplIssueTypes]
Reorganizing indexes for [dbo].[tpwLocationGroups]
Msg 1943, Level 16, State 1, Line 1
The index "IDX_tpwLocationGroups_CID" on table "tpwLocationGroups" cannot be reorganized because page level locking is disabled.

Does anyone know how I can modify the script (or a different one) to skip over indexes that cannot do this operation and continue to the next table?

Thanks

推荐答案

ALTER INDEX < Index name >  ON   < Table Name >
SET (
    ALLOW_PAGE_LOCKS = ON
)
GO

这将起作用

This will work

这篇关于SQL Server重组所有表中的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！