topic Re: Reindex GDB Tables in Data Management Questions

Reindex GDB Tables

CodyPatterson — Thu, 14 Mar 2024 12:08:41 GMT

Hey All,

Enterprise 11.2 Geodatabase.

I have been reindexing our Enterprise Geodatabase every day, as we write quite often to this DB, and have noticed using a fragmentation query, that we have a few tables that don't seem to reindex, these include GDB_ITEMS and SDE_column_registry. These are unable to be selected via ArcGIS Pro, so I was curious if these should be reindexed, or if these should just be disregarded, they are quite large.

Thanks in advance!

Cody

Re: Reindex GDB Tables

KimberleeHermann1 — Thu, 14 Mar 2024 15:51:52 GMT

Hello...if you are using SQL Server it is super easy to use the Maintenance Plan wizard and do a reindex of everything on a database. Usually doesn't take long at all. I have it scheduled for all of my databases each weekend and that seems to keep thing "all good".

Re: Reindex GDB Tables

MarceloMarques — Thu, 14 Mar 2024 16:16:12 GMT

@CodyPatterson - I usually avoid using the ArcGIS Pro GP Tools to gather new statistics and to rebuild indexes (attribute indexes and spatial indexes ) in my production geodatabases because these tools to not offer advanced options that you find with the SQL Server SQL Commands to do the same. I suggest you use the SQL Server Tools to do this maintenance.

You can find my maintenance scripts for SQL Server in my community.esri.com blog below, download the database template scripts for SQL Server.

Mapping and Charting Solutions (MCS) Enterprise Da... - Esri Community

Your question about the GDB_ITEMS table and any other ArcSDE Repository Tables. Yes, those tables must be reindexed as well, probably not every day, as you will not be creating new featureclasses, etc. in the geodatabase, but you should reorganize indexes weekly and rebuild indexes monthly at least for the ArcSDE Repository Tables, and right after that gather new statistics.

For reference to others about this discussion follow below the links for the ArcGIS Pro GP Tools.

Enterprise geodatabase maintenance tasks—ArcGIS Pro | Documentation
Modify a spatial index—ArcGIS Pro | Documentation
Rebuild Indexes (Data Management)—ArcGIS Pro | Documentation
Update database statistics—ArcGIS Pro | Documentation

I hope this helps.

Re: Reindex GDB Tables

CodyPatterson — Thu, 14 Mar 2024 16:14:37 GMT

Hey @KimberleeHermann1

Thank you for that tip, I was not sure if the SQL way would be preferable, or the ArcGIS Pro way, but it seems that the SQL way is more preferred! I will give that a shot and go through with it that way, my initial assumption was that since they are ArcGIS Pro provided indexes through GDB functionality, then they could only be rebuilt within Pro, but we learn as we go!

Cody

Re: Reindex GDB Tables

CodyPatterson — Thu, 14 Mar 2024 16:17:37 GMT

Hey @MarceloMarques

Thank you for the tip! I will check that out to see what I can manage with that! I spoke with another commenter and said that I was not confident on which method to use, since my assumption was that since it's a Pro index, I must do it in Pro, but it looks like that's incorrect.

I appreciate the documentation too!

Cody

Re: Reindex GDB Tables

KimberleeHermann1 — Thu, 14 Mar 2024 18:58:27 GMT

You are very welcome!! So glad it worked for you.

Re: Reindex GDB Tables

ModyBuchbinder — Mon, 19 Jan 2026 11:40:51 GMT

Hello all

I am running in similar program.

I use Maintenance Plan to check and index my tables.

When it try to reindex GDB_ITEMS I get this error:

ALTER INDEX [R6_pk] ON [sde].[GDB_ITEMS] REORGANIZ..." failed with the following error: "The index "R6_pk" on table "GDB_ITEMS" cannot be reorganized because
page level locking is disabled."

We did not changed anything in the system tables.

Any idea?

Re: Reindex GDB Tables

MarceloMarques — Tue, 20 Jan 2026 16:55:01 GMT

This is well known, and the change to the indexes was implemented many releases ago when Geodatabase Branch Versioning was introduced. Refer to my earlier reply above for links to my database maintenance scripts.

Examples:

- execute every month - rebuild indexes

CREATE PROCEDURE [dbo].[mp_rebuild_indexes_gisgdb] @fillfactor INT = 75 AS BEGIN DECLARE @DbId VARCHAR(255) DECLARE @Database VARCHAR(255) DECLARE @SchemaName VARCHAR(256) DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @cmd NVARCHAR(500) --DECLARE @fillfactor INT = 75 DECLARE DatabaseCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT dbid, name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model') AND name = 'gisgdb' ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database WHILE @@FETCH_STATUS = 0 BEGIN PRINT ('DB: '+ @Database) SET @cmd = 'USE ' + @Database + ';' PRINT (@cmd) EXEC (@cmd) PRINT ('Indexes allow_page_locks = 1') --Indexes allow_page_locks = 1 SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId +') schema_name, t.name table_name, i.name index_name FROM ' + @Database + '.sys.indexes i INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id WHERE i.allow_page_locks = 1 AND t.Name IS NOT NULL AND i.name IS NOT NULL' -- create index cursor PRINT (@cmd) EXEC (@cmd) OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName WHILE @@FETCH_STATUS = 0 BEGIN PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName) BEGIN TRY --SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD;' PRINT @cmd EXEC (@cmd) END TRY BEGIN CATCH PRINT 'This is the error: ' + error_message() END CATCH FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName END CLOSE IndexCursor DEALLOCATE IndexCursor PRINT ('Indexes allow_page_locks = 0') --Indexes allow_page_locks = 0 SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId + ') schema_name, t.name table_name, i.name index_name FROM ' + @Database + '.sys.indexes i INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id WHERE i.allow_page_locks = 0 AND t.Name IS NOT NULL AND i.name IS NOT NULL' -- create index cursor PRINT (@cmd) EXEC (@cmd) OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName WHILE @@FETCH_STATUS = 0 BEGIN PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName) BEGIN TRY SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = ON);' PRINT @cmd EXEC (@cmd) --SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REBUILD;' PRINT @cmd EXEC (@cmd) SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = OFF);' PRINT @cmd EXEC (@cmd) END TRY BEGIN CATCH PRINT 'This is the error: ' + error_message() END CATCH FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName END CLOSE IndexCursor DEALLOCATE IndexCursor FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor END GO

- execute every week - reorganize indexes

CREATE PROCEDURE [dbo].[mp_reorganize_indexes] AS BEGIN DECLARE @DbId VARCHAR(255) DECLARE @Database VARCHAR(255) DECLARE @SchemaName VARCHAR(256) DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @cmd NVARCHAR(500) DECLARE DatabaseCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT dbid, name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model') --AND name = 'topo2' ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database WHILE @@FETCH_STATUS = 0 BEGIN PRINT ('DB: '+ @Database) SET @cmd = 'USE ' + @Database + ';' PRINT (@cmd) EXEC (@cmd) PRINT ('Indexes allow_page_locks = 1') --Indexes allow_page_locks = 1 SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId +') schema_name, t.name table_name, i.name index_name FROM ' + @Database + '.sys.indexes i INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id WHERE i.allow_page_locks = 1 AND t.Name IS NOT NULL AND i.name IS NOT NULL' -- create index cursor PRINT (@cmd) EXEC (@cmd) OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName WHILE @@FETCH_STATUS = 0 BEGIN PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName) BEGIN TRY SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE WITH ( LOB_COMPACTION = ON );' PRINT @cmd EXEC (@cmd) END TRY BEGIN CATCH PRINT 'This is the error: ' + error_message() END CATCH FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName END CLOSE IndexCursor DEALLOCATE IndexCursor PRINT ('Indexes allow_page_locks = 0') --Indexes allow_page_locks = 0 SET @cmd = 'DECLARE IndexCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME(t.object_id,' + @DbId + ') schema_name, t.name table_name, i.name index_name FROM ' + @Database + '.sys.indexes i INNER JOIN ' + @Database + '.sys.tables t ON t.object_id= i.object_id WHERE i.allow_page_locks = 0 AND t.Name IS NOT NULL AND i.name IS NOT NULL' -- create index cursor PRINT (@cmd) EXEC (@cmd) OPEN IndexCursor FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName WHILE @@FETCH_STATUS = 0 BEGIN PRINT ('Index_Name: ' + @Database + '.' + @SchemaName + '.' + @TableName + '.' + @IndexName) BEGIN TRY SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = ON);' PRINT @cmd EXEC (@cmd) SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' REORGANIZE WITH ( LOB_COMPACTION = ON );' PRINT @cmd EXEC (@cmd) SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON ' + @Database + '.' + @SchemaName + '.' + @TableName + ' SET (ALLOW_PAGE_LOCKS = OFF);' PRINT @cmd EXEC (@cmd) END TRY BEGIN CATCH PRINT 'This is the error: ' + error_message() END CATCH FETCH NEXT FROM IndexCursor INTO @SchemaName,@TableName,@IndexName END CLOSE IndexCursor DEALLOCATE IndexCursor FETCH NEXT FROM DatabaseCursor INTO @DbId,@Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor END GO