Database Maintenance | Aranda Field Service

The maintenance of a database always like that of Aranda FIELDE SERVICE AFLS, is very important to keep all the information updated and at an optimal level. These maintenance operations may be carried out twice a month. It is also possible to modify the frequency of maintenance, taking into account the activity that the database receives in production and the volume of data it handles. This maintenance is recommended to be carried out twice a month, unless the level of transactions is very high.

TRANSACTION LOG:

Specify a log file growth limit.
Specify a log file growth rate that prevents a log file expansion operation from exceeding 2 seconds. You can normally use between 30 Mb and 60 Mb.
If you are using the full recovery model, define a recovery and backup strategy based on the transaction log, to make it easier to reuse the space provided by the log files.
If you use the simple recovery model, keep in mind that the transaction log will grow slowly, but it could affect the recovery strategy you have defined for the database.

DEFRAGMENTING INDEXES:

Index defragmentation tasks help improve database performance by improving access to data on disk; It is advisable to perform these tasks periodically to ensure the proper functioning of the database. These tasks help the execution plans to be compiled with the latest statistics from the database.

Note: Be sure to measure the impact of third-party scripting operations on your databases. Under no circumstances will Aranda SOFTWARE be held responsible for any damage caused to the information or the database schema after the execution of scripts that have not been referenced by authorized personnel of Aranda SOFTWARE.

If you want to defragment indexes and update statistics, do the following:

1. Make sure no users are editing, querying, or entering data into the Aranda suite tools. You can stop Internet Information Services (IIS) for this.

2. Stop the Aranda Suite services on the application server.

3. Run the script (Index Defragmentation and Statistics Update Script). It is not recommended to update statistics too frequently as this invalidates the execution plans stored in the server cache. For this reason, in the script you will find the parameter @statistics with the value ‘OFF’. When you want to update the statistics, change the mentioned parameter to the value ‘ON’.

4. Restart all Aranda Suite and Internet Information Services (IIS) services.

--Script desfragmentación de índices y actualización de estadísticas 
use DATABASE --Nombre de la base de datos
declare @statistics nvarchar(3) = 'OFF'; --Modifique este valor a 'ON' para actualizar estadísticas.
declare @sentence nvarchar(128),
@initdate datetime,
@inaldate datetime;
declare cur_indices cursor for with index_fragment (object_id, index_id, avg_fragmentation_in_percent) as (
  SELECT
    object_id,
    index_id,
    avg_fragmentation_in_percent
  FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
  WHERE
    (
      (avg_fragmentation_in_percent > 15)
      OR (avg_page_space_used_in_percent < 60)
    )
    AND page_count > 5
    AND index_id NOT IN(0)
    AND object_id NOT IN(
      	OBJECT_ID('AFLS_APP_LOG'),
      	OBJECT_ID('AFLS_ASSIGNMENT_ENGINE_LOG'),
OBJECT_ID('AFLS_MAPS_API_USED_LOG'),
      	OBJECT_ID('AFLS_LOG_CRUD_ADDITIONAL_FIELD'),
	OBJECT_ID('AFW_AUDIT'),
	OBJECT_ID('AFW_MAIL_HISTORY'),
	OBJECT_ID('AFW_WORKER_LOG')
    )
)
SELECT
  'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.' + OBJECT_NAME(t.object_id) + ' REBUILD' sentence
FROM
  index_fragment t
  INNER JOIN sys.indexes ind ON t.object_id = ind.object_id
  AND t.index_id = ind.index_id
  INNER JOIN sys.objects ob ON t.object_id = ob.object_id
  INNER JOIN sys.schemas sc ON sc.schema_id = ob.schema_id
Union

select
  'update statistics ' + OBJECT_NAME(object_id)
from
  index_fragment
where
  @statistics = 'ON' open cur_indices fetch cur_indices into @sentence while(@@FETCH_STATUS = 0) begin
set
  @initdate = GETDATE();
exec sp_executesql @sentence;
print @sentence + ' tiempo de ejecución: ' + convert(
  varchar(10),
  datediff(millisecond, @initdate, GETDATE())
);
fetch cur_indices into @sentence;
end close cur_indices;
deallocate cur_indices;
declare cur_indices cursor for with index_fragment (object_id, index_id, avg_fragmentation_in_percent) as (
  SELECT
    object_id,
    index_id,
    avg_fragmentation_in_percent
  FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED')
  WHERE
    (
      (
        avg_fragmentation_in_percent > 10
        AND avg_fragmentation_in_percent < 15
      )
      OR (
        avg_page_space_used_in_percent < 75
        AND avg_page_space_used_in_percent > 60
      )
    )
    AND page_count > 5
   AND dm_db_index_physical_stats.index_id NOT IN (0)
   AND object_id NOT IN(
      OBJECT_ID('AFLS_APP_LOG'),
      	OBJECT_ID('AFLS_ASSIGNMENT_ENGINE_LOG'),
OBJECT_ID('AFLS_MAPS_API_USED_LOG'),
      	OBJECT_ID('AFLS_LOG_CRUD_ADDITIONAL_FIELD'),
	OBJECT_ID('AFW_AUDIT'),
	OBJECT_ID('AFW_MAIL_HISTORY'),
	OBJECT_ID('AFW_WORKER_LOG')
    )
)
SELECT
  'ALTER INDEX ' + ind.[name] + ' ON ' + sc.[name] + '.' + OBJECT_NAME(t.object_id) + ' REORGANIZE' sentence
FROM
  index_fragment t
  INNER JOIN sys.indexes ind ON t.object_id = ind.object_id
  AND t.index_id = ind.index_id
  INNER JOIN sys.objects ob ON t.object_id = ob.object_id
  INNER JOIN sys.schemas sc ON sc.schema_id = ob.schema_id
union
select
  'update statistics ' + OBJECT_NAME(object_id)
from
  index_fragment
where
  @statistics = 'ON' open cur_indices fetch cur_indices into @sentence while(@@FETCH_STATUS = 0) begin
set
  @initdate = GETDATE();
exec sp_executesql @sentence;
print @sentence + ' tiempo de ejecución: ' + convert(
  varchar(10),
  datediff(millisecond, @initdate, GETDATE())
);
fetch cur_indices into @sentence;
end close cur_indices;
deallocate cur_indices;
GO