sqlmaint Utility

The sqlmaint utility performs a specified set of maintenance operations on one or more databases. Use sqlmaint to run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes. All database maintenance activities generate a report that can be sent to a designated text file, HTML file, or e-mail account.

Syntax

sqlmaint
[-?] |
[
    [-S server]
    [-U login_ID [-P password]]
    {
        [ -D database_name | -PlanName name | -PlanID guid ]
        [-Rpt text_file [-DelTxtRpt <time_period>] ]
        [-To operator_name]
        [-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
        [-RmUnusedSpace threshold_percent free_percent]
        [-CkDB | -CkDBNoIdx]
        [-CkAl | -CkAlNoIdx]
        [-CkTxtAl]
        [-CkCat]
        [-UpdSts]
        [-UpdOptiStats sample_percent]
        [-RebldIdx free_space]
        [-WriteHistory]
        [
            {-BkUpDB [backup_path] | -BkUpLog [backup_path] }
            {-BkUpMedia
                {DISK [    [-DelBkUps <time_period>]
                            [-CrBkSubDir ] [ -UseDefDir ]
                         ]
                | TAPE
                }
            }
            [-BkUpOnlyIfClean]
            [-VrfyBackup]
        ]
    }
]

<time_period> ::=
number[minutes | hours | days | weeks | months]

Note The parameters and their values must be separated by a space. For example, there must be a space between -S and server.

Arguments
-?
Specifies that the syntax diagram for sqlmaint be returned. This parameter must be used alone.
-S server
Specifies the target server. If not supplied, Microsoft® SQL Server™ is assumed to be on the local computer. SQL Server cannot be version 6.5 or earlier. Use the 6.5 version of sqlmaint for earlier versions of SQL Server.
-U login_ID
Specifies the login ID to use when connecting to the server. If not supplied, sqlmaint attempts to use Windows NT Authentication. If login_ID contains special characters, it must be enclosed in double quotation marks (“); otherwise the double quotation marks are optional.
-P password
Specifies the password for the login ID. Only valid if the -U parameter is also supplied. If password contains special characters, it must be enclosed in double quotation marks; otherwise the double quotation marks are optional.
-D database_name
Specifies the name of the database in which to perform the maintenance operation. If database_name contains special characters, it must be enclosed in double quotation marks; otherwise the double quotation marks are optional.
-PlanName name
Specifies the name of a database maintenance plan defined using the Database Maintenance Plan Wizard. The only information sqlmaint uses from the plan is the list of the databases in the plan. Any maintenance activities you specify in the other sqlmaint parameters are applied to this list of databases. You can get the plan name from SQL Server Enterprise Manager.
-PlanID guid
Specifies the globally unique identifier (GUID) of a database maintenance plan defined using the Database Maintenance Plan Wizard. The only information sqlmaint uses from the plan is the list of the databases in the plan. Any maintenance activities you specify in the other sqlmaint parameters are applied to this list of databases. This must match a plan_id value in msdb.dbo.sysdbmaintplans.
-Rpt text_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. The report maintains version information by adding a date to the file name. The date is generated as follows: at the end of the file name but before the period, in the form _yyyyMMddhhm. Yyyy = year, MM = month, dd = day, hh = hour, mm = minute.

If you run the utility at 10:23 A.M. on December 1, 1996, and this is the text_file value:

c:\mssql7\backup\Nwind_maint.rpt

  

The generated file name is:

c:\mssql7\backup\Nwind_maint_199612011023.rpt

The full UNC file name is required for text_file when sqlmaint accesses a remote server.

-DelTxtRpt <time_period>
Specifies that any text report in the report directory is to be deleted if the time interval after the creation of the report file exceeds the <time_period>.

-DelTextRpt looks for files whose name fits the pattern generated from the text_file parameter. If text_file is C:\Mssql7\Backup\Nwind_maint.rpt, then -DelTxtRpt causes sqlmaint to delete any files whose names match the pattern C:\Mssql7\Backup\Nwind_maint*.rpt and that are older than the specified <time_period>.

-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 Server Enterprise Manager.
-HtmlRpt html_file
Specifies the full path and name of the file into which an HTML report is to be generated. sqlmaint generates the file name by appending a string of the format _yyyyMMddhhmm to the file name, just as it does for the -Rpt parameter.

The full UNC file name is required for html_file when sqlmaint accesses a remote server.

-DelHtmlRpt <time_period>
Specifies that any HTML report in the report directory is to be deleted if the time interval after the creation of the report file exceeds <time_period>.

-DelHtmlRpt looks for files whose name fits the pattern generated from the html_file parameter. If html_file is C:\Mssql7\Backup\Nwind_maint.htm, then -DelHtmlRpt causes sqlmaint to delete any files whose names match the pattern C:\Mssql7\Backup\Nwind_maint*.htm and that are older than the specified <time_period>.

-RmUnusedSpace threshold_percent free_percent
Specifies that unused space be removed from the database specified in -D. This option is only useful for databases that are defined to grow automatically. Threshold_percent specifies in megabytes the size that the database must reach before sqlmaint attempts to remove unused data space. If the database is smaller than the threshold_percent, no action is taken. Free_percent specifies how much unused space must remain in the database, specified as a percentage of the final size of the database. For example, if a 200 MB database contains 100 MB of data, specifying 10 for free_percent results in the final database size being 110 MB. Note that a database will not be expanded if it is smaller than free_percent plus the amount of data in the database. For example, if a 108 MB database has 100 MB of data, specifying 10 for free_percent will not expand the database to 110 MB; it will remain at 108 MB.
-CkDB | -CkDBNoIdx
Specifies that a DBCC CHECKDB statement or a DBCC CHECKDB statement with the NOINDEX option be run in the database specified in -D. For more information, see DBCC CHECKDB.A warning is written to text_file if the database is in use when sqlmaint runs.
-CkAl | -CkAlNoIdx
Specifies that a DBCC NEWALLOC statement or a DBCC NEWALLOC statement with the NOINDEX option be run in the database specified in -D. For more information, see DBCC NEWALLOC.-CkTxtAl

Specifies that a DBCC TEXTALL statement be run in the database specified in -D. For more information, see DBCC TEXTALL.-CkCat

Specifies that a DBCC CHECKCATALOG statement be run in the database specified in -D. For more information, see DBCC CHECKCATALOG.-UpdSts

Specifies that the following statement is to be run on each table in the database:

UPDATE STATISTICS table

  

For more information, see UPDATE STATISTICS.-UpdOptiStats sample_percent

Specifies that the following statement is to be run on each table in the database:

UPDATE STATISTICS table WITH SAMPLE sample_percent PERCENT

  

For more information, see UPDATE STATISTICS.-RebldIdx 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 fill factor. For example, if free_space percentage is 30, then the fill factor used is 70. If a free_space percentage value of 100 is specified, then the indexes are rebuilt with the original fill factor value.

-WriteHistory
Specifies that an entry is made in msdb.dbo.sysdbmaintplan_history for each maintenance action performed by sqlmaint. If -PlanName or -PlanID are specified, the entries in sysdbmaintplan_history use the ID of the specified plan. If -D is specified, the entries in sysdbmaintplan_history are made with zeroes for the plan ID.
-BkUpDB [backup_path] | -BkUpLog [backup_path]
Specifies a backup action. -BkUpDb backs up the entire database. -BkUpLog backs up only the transaction log.

[backup_path] specifies the directory for the backup. [backup_path] is not needed if -UseDefDir is also specified, and is overriden by -UseDefDir if both are specified. The backup can be placed in a directory or a tape device address (for example, \\.\TAPE0). The file name for a database backup is generated automatically as follows:

dbname_db_yyyyMMddhhmm.BAK

  

where

The file name for a transaction backup is generated automatically with a similar format:

dbname_log_yyyymmddhhmm.BAK

  

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

-BkUpMedia
Specifies the media type of the backup.
DISK
Specifies that the backup medium is disk.
-DelBkUps <time_period>
Specifies that any backup file in the backup directory is to be deleted if the time interval after the creation of the backup exceeds the <time_period>.
-CrBkSubDir
Specifies that a subdirectory be created in the [backup_path] directory or in the default backup directory if -UseDefDir is also specified. The name of the subdirectory is generated from the database name specified in
-D. -CrBkSubDir offers an easy way to put all the backups for different databases into separate subdirectories without having to change the [backup_path] parameter.
-UseDefDir
Specifies that the backup file be created in the default backup directory. UseDefDir overrides [backup_path] if both are specified. With a default SQL Server setup, the default backup directory is C:\Mssql7\Backup.
TAPE
Specifies that the backup medium is tape.
-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 in the command prompt. 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.
-VrfyBackup
Specifies that RESTORE VERIFYONLY is run on the backup when it completes.
number[minutes | hours | days | weeks | months]
Specifies the time interval used to determine if a report or backup file is old enough to be deleted. number is an integer, for example, 12weeks, 3months, or 15days. If only number is specified, the default date part is weeks.
Remarks

sqlmaint performs maintenance operations on one or more databases. If -D is specified, the operations specified in the remaining switches are performed only on the specified database. If -PlanName or -PlanID are specified, the only information sqlmaint retrieves from the specified maintenance plan is the list of databases in the plan.  All operations specified in the remaining sqlmaint parameters are applied against each database in the list obtained from the plan. sqlmaint does not apply any of the maintenance activities defined in the plan itself.

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

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

regsvr32 c:\mssql7\binn\sqldmo.enu

  

The sqlmaint utility returns 0 if it runs successfully, or 1 if it fails. Failure is reported:

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

Examples
A. Perform DBCC checks on the Northwind database. Create a text report, and delete any existing reports more than 3 months old

sqlmaint -S MyServer -U "sa" -P "SaPwd" -D Northwind -CkDB -CkAl -CkTxtAl -CkCat -Rpt C:\MyReports\Nwind_chk.rpt -DelRpt 3months

  

B. Update statistics using a 15% sample in all databases in a plan. Also, shrink any of the database that have reached 110 MB down to having only 10% free space

sqlmaint -S MyServer -U "sa" -P "SaPwd" -PlanName MyUserDBPlan -UpdOptiStats 15 -RmUnusedSpace 110 10

  

C. Backup all the databases in a plan to their individual subdirectories in the default C:\Mssql7\Backup directory. Also, delete any backups older than 2 weeks

sqlmaint -S MyServer -U "sa" -P "SaPwd" -PlanName MyUserDBPlan -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.