Breadcrumbs

How-to - Configure SQL Maintenance Plans on SQL database(s) for SQL based Source Systems Plan

Aim

The CI Sync Agent is a Windows Service that uses a SQL Server engine to track Assets that need to sync via CI Sync. Various SQL based source systems also leverage a SQL Server Database (e.g. The Lansweeper and SCCM Source Connectors).

This article guides the user on how to setup a Rebuild Index Maintenance plan on SQL Server to maximise performance via minimising index fragmentation.

This KB applies to:

Applies to Versions

All

Applies to Source Connectors

Lansweeper On-Prem, SCCM

Applies to Destination Connectors

All

Process

Important Note

Some versions of SQL Server SSMS 21 did not support Maintenance Plans - see the blog post below on how to address this.

Announcing the release of SQL Server Management Studio (SSMS) 21.2.5 | Microsoft Community Hub

This guide assumes:

  • SQL Server Management Studio (SSMS) is installed and setup

  • SSMS is running on the same server as the RecVer and Lansweeper databases

Step 1 - Setup a Rebuild Index Maintenance Plan

The scope of the maintenance plan should include the following:

  1. The CI Sync RecVer DB and

  2. Any other SQL based source systems being used by CI Sync (e.g. Lansweeper or SCCM)


  1. Login to SQL Server and open SQL Server Management Studio and expand Databases

    image-20250115-043054.png

  

  1. Expand Management then click on Maintenance Plans

    image-20250115-043116.png


  2. Right click on Maintenance Plans and click New Maintenance Plan

    image-20250115-043200.png


  3. Name the plan Daily Rebuild Index or similar and click OK.

    image-20250115-043227.png


  4. On the left of SQL click on Toolbox and expand Maintenance Plan Tasks

    Note: If you cannot see Toolbox then on the SQL menu click View then Toolbox.

    image-20250115-043257.png


  5. Click on Rebuild Index Task and drag it to the new plan

    image-20250115-043317.png


  6. Double Click on the Rebuild Index Task then:

    1. Drop down Databases

    2. Select the RecVer database

      1. If using Lansweeper, Syncfish recommend you also select the lansweeperdb database

      2. If using SCCM, Syncfish recommend you also select the sccm database

    3. Click OK

    4. Then again click OK

      image-20250115-043437.png


  7. Click on the Schedule and set to Daily and chose a Time of Day to run the job.  Then click OK

Choose a time that does not clash with Backups and other tasks scheduled on SQL Server.

image-20250115-043511.png

  1. Click Save. The maintenance plan has been setup.

    image-20250115-043656.png

Step 2 - Setup a Refresh Statistics Maintenance Plan

The scope of the maintenance plan should include the following:

  1. The CI Sync RecVer DB and

  2. Any other SQL based source systems being used by CI Sync (e.g. Lansweeper or SCCM)

  1. Login to SQL Server and open SQL Server Management Studio and expand Databases

    image-20250115-043054.png

  

  1. Expand Management then click on Maintenance Plans

    image-20250115-043116.png


  2. Right click on Maintenance Plans and click New Maintenance Plan

    image-20250115-043200.png


  3. Name the plan Daily Update Statistics or similar and click OK.

    image-20250326-065407.png

  4. On the left of SQL click on Toolbox and expand Maintenance Plan Tasks

    Note: If you cannot see Toolbox then on the SQL menu click View then Toolbox.

    image-20250326-065431.png

  5. Click on Update Statistics Task and drag it to the new plan

    image-20250326-065508.png

  6. Double Click on the Update Statistics Task then:

    1. Drop down Databases

    2. Select the RecVer database

      1. If using Lansweeper, Syncfish recommend you also select the lansweeperdb database

      2. If using SCCM, Syncfish recommend you also select the sccm database

    3. Click OK

    4. Then again click OK

      image-20250326-065550.png

  7. Click on the Schedule and set to Daily and chose a Time of Day to run the job.  Then click OK

Choose a time that does not clash with Backups and other tasks scheduled on SQL Server.

image-20250326-065611.png

  1. Click Save. The maintenance plan has been setup.

image-20250326-065625.png

  1. Refresh the Maintenance Plans and expand to confirm the new job appears in the list (i.e. the new job has been created)

image-20250326-065702.png

Step 3 - Confirm the CI Sync RecVer Database is in Simple Recovery Mode

Simple recovery mode prevents the creation of transaction log files which could impact disk space over time.

  1. Login to SQL Server and open SQL Server Management Studio and expand Databases, then

    1. Right-click on the CI Sync RecVer database (named accordingly when it was created as part of setting up a Source System Connection during Step 5) and select Properties.

    2. Click the Options page

    3. Check the Recover Mode of the RecVer database and make sure it’s set to Simple as the Recovery Mode (if it isn’t then set to “Simple”)

    4. Click OK

image-20250326-070033.png

Step 4 - Validate the Maintenance Plan is effective

The final step is to validate the Maintenance Plan does its job.

  • Does the Maintenance Plan effective rebuild and defrag indexes effectively?
    Validate that indexes on the Lansweeper and CI Sync Rec Ver DBs do not exhibit a high level of index fragmentation. You should aim for 50% fragmentation or less as a good target. If fragmentation still remains over 50%, especially on the tables with many records, review the Maintenance Plan.

  • Does the Maintenance Plan update database statistics?

Run some checks after maintenance plan execution to verify this. Some example detection scripts are below to identify fragmentation and statistics freshness.

Links


Other Suggestions for SQL Database Health

Your SQL DBA may want to consider the following additional jobs.

  1. MSDB history clean-up

  2. Database integrity checks

  3. Job report file clean-up (i.e. a purge of report files created by the job)


See also:

https://learn.microsoft.com/en-us/sql/relational-databases/maintenance-plans/create-a-maintenance-plan?view=sql-server-ver16

There are currently no related articles.

Control Information

Created

Reviewed

Data Classification

PUBLIC
Classified in accordance with the Syncfish Data Classification Framework