Create the index defragmentation task

Over time, database indices become fragmented, leading to slower read/write operations. This task sets up an agent job to defragment indices and maintain performance:

1. Download and review the queries included in the zip file attached to this article.
2. Run these queries against the Commander database in the following sequence:
   • CommandLog.sql
   • CommandExecute.sql
   • IndexOptimize.sql
     These create tables and a stored procedure to schedule defragmentation.
3. In Object Explorer, expand SQL Server Agent.
4. Right-click Jobs and select New Job…
5. On the General page, provide the Name Defrag Commander DB.
6. Switch to the Steps page and click New…
7. Enter the Step Name Execute Command.
8. Set Type to Transact-SQL script (T-SQL).
9. Set the database to the Commander database.
10. Enter the following command (replace commander with your database name), then click OK:

    EXECUTE [dbo].[IndexOptimize] @Databases = 'commander', @LogToTable = 'Y', @TimeLimit = '3600', @UpdateStatistics = 'ALL'

11. Switch to the Schedules page and configure a recurring schedule. Flexera recommends running this weekly during periods of low activity.
12. Optionally, configure notifications under the Notifications page, such as writing to the Windows Application event log on failure or other preferred options.
13. Click OK.

Create the database corruption detection task

Adding a database integrity check protects against operating with corrupted data. Flexera recommends running these checks during maintenance windows because they are resource-intensive:

1. In Object Explorer, expand SQL Server Agent.
2. Right-click Jobs and select New Job…
3. On the General page, provide the Name Commander DB Corruption Check.
4. Switch to the Steps page and click New…
5. Enter the Step Name Execute Command.
6. Set Type to Transact-SQL script (T-SQL).
7. Set the database to the Commander database.
8. Enter the following command (replace commander with your database name), then click OK:

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

9. Switch to the Schedules page and set it up for a maintenance window.
10. Configure notifications under Notifications, such as writing to the Windows Application event log on failure or setting other options as needed.
11. Click OK.