Database indexes rebuilding

Rebuilding database indexes and updating the statistics is mandatory for BigFix Inventory to operate smoothly. If you experience a delay in UI response or importing data, make sure to defragment your hard disk and that statistics are up to date.

From version 10.0.4, for new installations and upgrades, a job is included in MS SQL database named “<InventoryDB> Full database Index Reorganization.” This job conducts required index maintenance.

The job follows the below criteria:

  • The script runs everyday at 4 AM local time.
  • If a data import is running (scheduled import or manual import), the job waits for the import to finish, and then attempts to rebuild the index. The job attempts to run 16 times after 30 minute intervals. The total time spent on waiting is 8 hours. If the import does not complete in 8 hours, the job exits the queue with an error message in the status log. In that case, the index is rebuilt the next day at the scheduled time.
  • If an index rebuilding job already exists with the name, "<InventoryDB> Full database Index Reorganization," then the scripts do not overwrite the data and the existing configuration is retained.

  • For the BigFix Inventory index rebuilding job to execute, the SQL Server Agent must be configured and running. The agent is turned off in some cases. You need to manually turn it on. To learn about how to configure and how to start SQL Server Agent, refer to Configure SQL Server Agent and Start, Stop, or Pause the SQL Server Agent Service respectively.

  • It is recommended to configure SQL Server Agent to restart automatically in case of failure. For more information, see Auto Restart SQL Server Agent.

For versions lower than 10.0.4, run the operation once a week to rebuild database.

The following example shows a simple approach to rebuild indexes and update statistics. The job rebuilds all indexes independent of their fragmentation. The script uses the standard database name as TEMADB.

      USE TEMADB
      GO
      IF EXISTS (SELECT
      *
      FROM dbo.imports
      WHERE success IS NULL)
      BEGIN
      PRINT N'CANNOT RUN index rebuild. BFI import is running!'
      PRINT N'Wait until BFI import finishes'
      END
      ELSE
      BEGIN
      DECLARE table_cursor CURSOR FOR
      SELECT
      table_schema,
      table_name
      FROM INFORMATION_SCHEMA.TABLES
      WHERE table_type = 'BASE TABLE'
      OPEN table_cursor
      DECLARE @tableName sysname
      DECLARE @tableSchema sysname
      FETCH NEXT FROM table_cursor INTO @tableSchema, @tableName
      WHILE @@fetch_status != -1
      BEGIN
      PRINT N'START alter index all on ' + @tableSchema
      + N'.' + @tableName + N' rebuild';
      EXECUTE (N'alter index all on ' + @tableSchema + N'.'
      + @tableName
      +
      N' rebuild')
      PRINT N'END alter index all on ' + @tableSchema
      + N'.' + @tableName + N' rebuild';
      FETCH NEXT FROM table_cursor INTO @tableSchema,
      @tableName
      END
      CLOSE table_cursor
      DEALLOCATE table_cursor
      PRINT N'START sp_updatestats';
      EXECUTE sp_updatestats
      PRINT N'END sp_updatestats';
      END
      GO