Company General Maintenance Utilities, Tools & Issues: Running a generic re-indexing of an Evolution company

Please note the following steps should preferably be applied by an Evolution support consultant. Therefore, please contact either your Evolution business partner or Evolution Support to assist.

1. If working in an Offline Branch Accounting environment, stop the local branch’s Sync Monitor.

2. Ensure all users are logged out of the Evolution company.

3. Back up the company.

4. Within MS SQL Management Studio, right-click on the relevant Evolution company database, copy the SQL script below, and execute it.

This script indicates the company database's fragmentation levels and you should notice in the results panel that these levels are fairly high, indicating a major reason for the slow syncing.

SELECT DB_NAME(ps.database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],i.name AS [Index Name],

ps.index_id, ps.index_type_desc, ps.avg_fragmentation_in_percent,ps.fragment_count, ps.page_count, i.fill_factor, i.has_filter, i.filter_definition FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL , N'LIMITED') AS ps

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON ps.[object_id] = i.[object_id]

AND ps.index_id = i.index_id

WHERE ps.database_id = DB_ID()

AND ps.page_count > 2500

ORDER BY ps.avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

The reason for running this script is to indicate the level of fragmentation in the company database as revealed by the number of records as well as the fragment_count column (marked below).

5. Also run the SQL script below on the relevant Evolution company database:

select isnull('ALTER INDEX [' + I.name + '] ON ' + T.name + ' REBUILD;','') as rebuild_index

from sys.indexes I

inner join sys.tables T on I.object_id = T.object_id

where I.name <> ''

 6. The results of running the script above should produce output such as displayed below (it may consist of up to 2087 rows):

7. Finally, all lines in the above results should now be simultaneously run as a new SQL script. To do this proceed as follows:

7.1 Highlight and copy all of the above result lines.

7.2 Paste it into the SQL script entry screen. 

7.3 Ensure all lines are highlighted and then press the F5 key on the keyboard or the Execute button on the toolbar, to commence with the complete database re-indexing process.

7.4 Allow for at least 2 or more hours (depending on the size of the company DB) for this process to be completed, before continuing to work in the company.

In smaller companies, this may be completed within minutes.

7.5 When successfully executed, the expected MS SQL Management Studio confirmation message should display on the results pane.

8. Finally, if you now rerun the script in step 4 above, you’ll notice no records identified in the results pane, indicating zero table defragmentation.