Create the defragmentation task

To reduce fragmentation and improve performance:

1. Download and run the queries included in the attached zip file against the Commander database in this order:
   • CommandLog.sql
   • CommandExecute.sql
   • IndexOptimize.sql
2. In Object Explorer, expand SQL Server Agent.
3. Right-click Jobs and select New Job…
4. On the General page, name the job Defrag Commander DB and set the Owner (typically commander connection user or sa).
5. Switch to the Steps page and click New…
6. Enter Step Name as Execute Command.
7. Set Type to Operating system (CmdExec) and Run as to SQL Server Agent Service Account.
8. Enter the following command (replace commander with your database name):

   sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d commander -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'commander', @LogToTable = 'Y', @TimeLimit = '3600', @UpdateStatistics = 'ALL'" –b

9. Configure notifications as needed (for example, write to Windows Application event log on failure).
10. Click OK twice to save.

Create the maintenance plan

Set up a plan to schedule recommended tasks during low-activity periods:

1. In Object Explorer, expand Management. Right-click Maintenance Plans and select New Maintenance Plan.
2. Provide a name and click OK.
3. Drag tasks from the Toolbox to the plan and link them in sequence using green arrows. Recommended tasks:
   • Index defragmentation and statistics updates
   • Corruption detection
   • Backup
   • History and maintenance cleanup
4. Open each task to configure options as described in their respective sections.
5. Click Schedule to set up a weekly run (Saturday or Sunday night is typical).
6. Save and close the plan.
   To run corruption detection manually:
   
   

   DBCC CHECKDB ('commander') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Backup recommendations

Perform nightly full database backups. Manual backups are also required before upgrades or major changes. Verify if third-party backup processes exist to avoid conflicts.

History and maintenance cleanup

Add a History Cleanup Task to remove records older than four weeks for:

• Backup and restore history
• SQL Server Agent job history
• Maintenance plan history
  Specify the folder storing backups and backup file extension (typically .bak).

Outcome

These steps ensure your Commander database runs efficiently, scales appropriately, and remains resilient against corruption while maintaining clean maintenance histories.