Reindexing - Errors

Epicor ERP 9
1

We are looking to run the following Reindex sql script below this weekend. In the past when we have tried running it, we’ve encountered the error message below. Any thoughts on what is causing the error message (i.e. all users need to be log out, appservers need to be down, etc.)? We have run this online without issues but just want to make sure I’m not missing something since this reindexing takes about 2 hrs to complete and don’t want to restart over again.

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q “EXECUTE dbo.IndexOptimize @Databases = ‘EpicorPilot905’, @FragmentationLow = NULL, @FragmentationMedium = ‘INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, @FragmentationHigh = ‘INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE’, @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @UpdateStatistics = ‘ALL’, @OnlyModifiedStatistics = ‘Y’, @MAXDOP = ‘4’” -b

Error message:
Date and time: 2017-04-22 22:14:26 Command: ALTER INDEX [idxsch] ON [EpicorPilot905].[dbo].[abthead] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: No, PageCount: 166953, Fragmentation: 20.3914 Outcome: Succeeded Duration: 00:02:19 Date and time: 2017-04-22 22:16:45 Date and time: 2017-04-22 22:17:00 Command: ALTER INDEX [sysindex] ON [EpicorPilot905].[dbo].[abthead] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: No, PageCount: 212469, Fragmentation: 20.6868 Outcome: Succeeded Duration: 00:02:22 Date and time: 2017-04-22 22:19:22 Date and time: 2017-04-22 22:1… Process Exit Code 1. The step failed.

2

The script is fine and the error is not really displayed. Since the ABT tables can be large my first guess would be disk space (log, data file, or tempdb).

3

Yes, our data file and tempdb are getting large. If this is the case, do we need to increase the disk space before the sql script runs successfully?

4

Absolutely. You need to look at the size of the index consumption and make sure you have at least that in free space within the data files or you are wasting time with the re-index. In this example below I would want about 10GB in free space and you need to manage your log files as well. You should increase by the size of the index space so it does not auto-grow and slow things down.

image
image.png769×298 15.7 KB

5

Does the reindex use the data file drive or the tempdb file drive? We are increasing the data file drive but not the tempdb drive looking for it to clear itself out. Will adding 30gb to the data file drive help with the reindex?

6

If you use the SortInTempdb flag it will use the tempdb

SortInTempdb
Use tempdb for sort operations when rebuilding indexes.

Value Description
Y Use tempdb for sort operations when rebuilding indexes.
N Do not use tempdb for sort operations when rebuilding indexes. This is the default.

Increasing the data file free space by 30GB uniformly will be a good thing