SQLMAINT.EXE Command-prompt Utility (version 6.5)

The SQLMAINT.EXE command-prompt utility performs routine database maintenance. Use SQLMAINT.EXE to run DBCC checks, to dump a database and its transaction log, to update statistics, and to rebuild indexes. All database maintenance activities generate a report that can be sent to a designated e-mail account.

Syntax

SQLMAINT
[-?] | [-S server_name]
    [-U login_ID [-P password]]
    -D database_name
    -Rpt output_file
    [-To operator_name]
    [-CkDB | -CkDBNoIdx]
    [-CkAl | -CkAlNoIdx]
    [-CkTxtAl]
    [-CkCat]
    [-UpdSts]
    [-RebldIdx free_space]
    [-BkUpDB backup_path | -BkUpLog backup_path]
    [-BkUpOnlyIfClean]
    [-BkUpMedia {DISK [-DelBkUps number_of_weeks] | TAPE}]

where

-?
Specifies that the syntax diagram for SQLMAINT.EXE utility be returned. This parameter must be used alone.
-D database_name
Specifies the target database name. This parameter is required if the -? parameter is not used.
-Rpt report_file
Specifies the full path and name of the file into which the report is to be generated. The report is also generated on the screen. This parameter is required if the -? parameter is not used. The report maintains version information by adding a date to the filename. The date is generated as follows: at the end of the filename but before the period, in the form _YYYYMMDD.

If you run the utility on Dec. 1, 1996 and this is the report_file value: 

c:\mssql\backups\pubs_maint.rpt

Then it becomes: 

c:\mssql\backups\pubs_maint_19961201.rpt

The full UNC filename is required for the -Rpt file when SQLMAINT.EXE accesses a remote server.

-S server
Specifies the target server. If this parameter is not supplied, SQL Server is assumed to be on the local machine. The SQL Server must be version 6.0 or later, though it is recommended that you run the utility on version 6.5. This parameter is optional.
-U login_ID
Specifies the login ID to use when connecting to the server. If this parameter is not supplied SQLMAINT.EXE will attempt to log in by using a trusted connection. This parameter is optional.
-P password
Specifies the password for the login ID. Only valid if the -U parameter is also supplied. This parameter is optional.
-To operator_name
Specifies the operator to whom the generated report will be sent through SQL Mail. The operator can be defined by using SQL Enterprise Manager. This parameter is optional.
-CkDB | -CkDBNoIdx
Specifies page checking against extent structures. The CkDB checks data and index pages against allocation units. CkDBNoIdx will check only the data pages against the extent structures.

Data storage is held in pages, 256 of which are grouped into one extent structure. At the beginning of each allocation unit, a page is dedicated to keeping track of the pages within the extent. Also, the pages are linked by pointers stored on the pages. This check tests to see that the pages linked by pointers match the pages listed on the first page in the extent structure.

A warning is written to an output file if the database is in use when SQLMAINT.EXE runs. This parameter is optional.

These parameters are equivalent to DBCC NEWALLOC and DBCC NEWALLOC with the NOINDEX option. For more information, see the DBCC statement.

-CkAI | -CkAlNoIdx
Is to check the allocation of text and image columns in the database and generates a report of allocation errors. This parameter is optional.
-CkCat
Is to check for consistency of the system tables and generates a report of all consistency errors. This parameter is optional.
-UpdSts
Is to update statistics about the distribution of key values in specified indexes for all tables in the target database. This parameter is optional.
-Rebldldx free_space
Specifies that indexes on tables in the target database should be rebuilt by using the Free Space percent value as the inverse of the fillfactor. For example, if Free Space percentage is 30 then the fillfactor used is 70. If the Free Space percentage value of 100 is specified, then the indexes are rebuilt with the original fillfactor value.

This parameter is optional and is available only with SQL Server version 6.5.

-BkUpDB backup_path | -BkUpLog backup_path
Specifies a backup action. BkUpDb backs up the entire database. BkUpLog backs up only the transaction log.

The backup_path specifies the directory for the backup. The backup can be placed in a directory or a tape device address (For example, \\.\TAPE0). The filename for a database backup is automatically generated as follows: 

dbname_db_dump.yyyymmdd

The filename for a transaction backup is automatically generated as follows: 

dbname_log_dump.yyyymmdd

If you use the -BkUpDB parameter you must also specify the media using the -BkUpMedia parameter.

-BkUpMedia {TAPE | DISK [DelBkUps num_weeks]}
Specifies the media type of the backup as either TAPE or DISK.
-DelBkUps number_of_weeks
Specifies the number of weeks to retain disk backup files. Backup files older than the specified number of weeks are deleted. This parameter can be used only if -BkUpType DISK is also specified.
-BkUpOnlyIfClean
Specifies that the backup occurs only if any specified -CK checks did not find problems with the data. Maintenance actions run in the same sequence as they appear on the command line. Specify the parameters -CkDB, -CkDBNoIdx, -CkAL, -CkAlNoIDX, -CkTxtAL, or -CkCat before the -BkUpDB/-BkUpLog parameter(s) if you are also going to specify -BkUpOnlyIfClean, or the backup will occur whether or not the check reports problems.

Remarks

SQLMAINT.EXE is a SQL-DMO application, which makes it necessary to register SQL-DMO on the machine where you intend to run SQLMAINT.EXE. If SQL Server or SQL Enterprise Manager are installed on the machine, SQL-DMO is already registered.

Type the following to register SQL-DMO at the command line: 

regsvr32 c:\mssql\binn\sqlole65

SQLMAINT.EXE returns 0 if it runs successfully or 1 if it fails. Failure is reported:

For example, SQLMAINT.EXE returns 1 if SQL-DMO has not been registered.