SQLMaint

The syntax is simply the following:

 SQLmaint 

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.

Some of the commonly used options for SQLMaint can be grouped as follows :

-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.

CAUTION

Report files that are determined by the “Rpt option that have a file extention other than TXT are not deleted when the DelTxtRpt option is specified. If the report file has the TXT extention, the deletion occurs as decribed previously.

TIP

The Database Maintenance Plan Wizard can be used to generate new scheduled jobs that utilize the xp_sqlmaint extended stored procedure. The xp_sqlmaint procedure calls the sqlmaint utility with a string containing sqlmaint switches. You can change the various options available in the Plan Wizard and review the SQLMAINT options that are created in the underlying scheduled jobs. The scheduled jobs associated with the Maintenance Plan will have a name that includes the name of the new Maintenance Plan that you created with the wizard.

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\ .