Do database maintenance on memoQ TMS's SQL database

Over time, indexes in memoQ TMS's SQL database might become fragmented – depending on how and how much memoQ TMS is used. This leads to poorer performance.

The Auto-maintenance tool rebuilds these indexes and keeps the database in proper shape.

How to get here

The Auto-maintenance tool is a command-line application, available from the Tools & Utilities tab of our Downloads page.

What can you do?

Use the runIndexDefrag command for this.

Syntax:

Option (short) Option (long) Required or not Description
-c

--connString

not required

Specify the database's connection string, if it is different from the value stored in %PROGRAMDATA%\MemoQ Server\Configuration.xml, in the
<SQLConnectionString> section.

The setting contains spaces, so it needs to be between quotation marks.

Example:

Run a one-time defragmenting action on the memoQ TMS database: 
./AutoMaintenanceTool.exe runIndexDefrag --connString "Initial Catalog=localhost\MEMOQSERVER;server=(local)"

You need a paid edition of SQL Server: The SQL Server Agent is not included in SQL Server Express Edition (what memoQ TMS installs by default.

The SQL Server Agent service must be running on the server machine where the memoQ TMS's database is. If it is not running, the Auto-maintenance tool creates the job, but it will only be scheduled when the SQL Server agent starts. To start it manually: Open SQL Server Configuration Manager, select SQL Server Services, right-click the service SQL Server Agent (MSSQLSERVER), and in the menu, click Start.

If the SQL Server Agent service is not installed on the server, the Auto-maintenance tool cannot schedule a job. You can create a task to do the same with the Windows Task Scheduler.

Use the scheduleIndexDefrag command for this. To change the schedule you created, run this command again.

Syntax:

Option (short) Option (long) Required or not Description
-c

--connString

not required

Specify the database's connection string, if it is different from the value stored in %PROGRAMDATA%\MemoQ Server\Configuration.xml, in the
<SQLConnectionString> section.

The setting contains spaces, so it needs to be between quotation marks.
-s --start not required Specifies start time for the job in HH:mm:ss (24-hour) format. If omitted, the default value 23:00:00 is used.
-e --end not required Specifies end time for the job in HH:mm:ss (24-hour) format. If omitted, the default value 23:59:59 is used.

Example:

Set up a defragmenting job that runs every day from 20:15 to 22:45: 
./AutoMaintenanceTool.exe scheduleIndexDefrag --connString "Initial Catalog=localhost\MEMOQSERVER;server=(local)" -s 20:15:00 -e 22:45:00

To remove the defragmentation schedule you created, use the cleanupIndexDefrag command with the -s option.

The Auto-maintenance tool creates work objects (for example, tables, views, and custom schemas) that the defragmenting process uses. These all have the idf database schema in their names. To remove those from the database (together with the schedule), use the cleanupIndexDefrag command without the -s option.

Note: The Auto-maintenance tool re-creates these work objects during the next defragmenting. Only clean them up if you have a reason for it.

Syntax:

Option (short) Option (long) Required or not Description
-c

--connString

not required

Specify the database's connection string, if it is different from the value stored in %PROGRAMDATA%\MemoQ Server\Configuration.xml, in the
<SQLConnectionString> section.

The setting contains spaces, so it needs to be between quotation marks.
-s --schedulingOnly not required

Specifies if the temporary database objects should be removed.

If omitted, the tool only removes the scheduling.

Example:

Remove the scheduled defragmenting task, but leave the temporary database objects: 
./AutoMaintenanceTool.exe cleanupIndexDefrag --connString "Initial Catalog=localhost\MEMOQSERVER;server=(local)" -s

If you cannot install or run the SQL Server Agent service on the server machine, use the Task Scheduler app in Windows:

  1. Sign in to the server machine where the memoQ TMS's database is. Use an account with administrator rights.

  2. Press the Windows key, and type 'task'.

  3. In the result list, click Task Scheduler. The Task Scheduler window opens.

  4. In the Actions pane, click the Create task command.

  5. On the General tab, fill the Name and Description fields as needed.

  6. On the Triggers tab, click the New button. The New trigger window opens.

  7. Set up a schedule for the defragmenting task. The Auto-maintenance tool will run for a maximum of 3 hours. If you need the task to stop after less time, check the Stop task if it runs longer than check box, and choose a shorter duration from the dropdown.

  8. Click the OK button.

  9. On the Actions tab, click the New button. The New action window opens.

  10. Next to the Program/script text box, click the Browse button, and find the Auto-maintenance tool's folder. Select the .exe file, and click OK.

  11. In the Add arguments (optional) text box, type runIndexDefrag. This argument is not optional: if you leave the field empty, the Auto-maintenance tool will not do anything.

  12. If you need to use a custom connection string, add -c plus the connection string after runIndexDefrag.

  13. Click the OK button.

  14. In the Create task window, click the OK button, too.

Your scheduled task appears in the Task Scheduler Library. It will run regularly, at the time you set up.

To run the task manually: Select it in the list, and in the Actions pane's Selected item section, click Run.