The SQLMaint utility is the power under the hood of the Database Maintenance Wizard. The SQL Server maintenance jobs that are created upon completion of the wizard use xp_sqlmaint to execute the SQLMaint utility with the options selected.
You can also set up and execute the SQLMaint utility independent of the wizard. All of the features and options that are available via the wizard can be manually set with the SQLMaint utility and used for setting up automated maintenance procedures for a given database.
-CK is the prefix used with any option that performs a data integrity check, such as checking the integrity of the database tables and indexes. Some of these options have the ability to bypass index checking, which speeds up the operation considerably. These options include -CkDB , -CkDBNoIDX , -CkAl , -CkAlNoIdx , and “CkCat .
-UpdOptiStats and -RebldIdx are options that improve query performance. The first option updates the statistics used by the query optimizer and the second option rebuilds the indexes entirely.
-BkUp is the prefix used with options that back up either the database or the transaction log. These options include “BkUpDB , -BkUpLog , - BkUpMedia , and -BkUpOnlyIfClean .
-DelBkUps determines how long the previous backups are saved before the utility deletes them. You can specify the time period that can be set for minutes, hours, days, weeks, or months.
-Rpt and -HtmlRpt are options that output the results of SQLMaint to a text file or an HTML file that can be published on a Web page.
-DelTxtRpt specifies a time period for which text reports will be deleted. Report files older than the specified time period that match the “Rpt text file pattern will be deleted.
The SQLMAINT.EXE file is located, by default, in a path that is specific to the SQL Server instance. Therefore, one EXE is installed for each instance that is created. The path will typically be Program Files\Microsoft SQL Server\MSSQL$ instance_name \Binn\ .