Lesson 10	sqlmaint.exe
Objective	Run sqlmaint.exe.

Run sqlmaint.exe in SQL-Server

Sqlmaint.exe is a command line program that can be used to run database maintenance commands from a command prompt. It can do the following

Run DBCC checks
Back up a database
Back up a transaction log
Update statistics
Rebuild indexes
Send reports to a text file, HTML file, or email account

Here is what the syntax of sqlmaint.exe looks like:

syntax of sqlmaint.exe

The following SlideShow explains the syntax of sqlmaint.exe. in line-by-line detail.

? generates a listing of the parameter.
S < Server Name> is the SQL Server to connect to
U< Login ID> is the SQL Server login to use when connecting to the SQL Server
D<Database Name> is the database to maintain Rpt <Output File> is the full path of the report file
To <Operator Name> is the account to email a report to
CkDB | - CkDBNoldx will run dbcc checked or dbcc checkdb with the noindex option
UpdSts will udpate the statistics on every table, index and statistic group in the database.
BkUpDB <Backup Path> backs up the database.
BkUpLog <Backup Path> backs up the transaction log.
UpdOptiStats <% of Database to Sample> allows you to specify how much of the database to sample when generating statistics.
PlanID <GUID> identifying the maintenance plan ... allows you to specify an existing database maintenance plan.
CrBkSubdir creates a sub-directory for storing the backup files

[-DelTxtRpt <time period> ]
[-DelHtmlRpt <time period> ]

SQL-Server sqlmaint.exe Syntax

1) ? generates a listing of the parameter
S<Server Name> is the SQL Server to connect to
U<Login ID> is the SQL Server login to use when connecting to the SQL Server
P <Password> indicates the password for the login

2) Database is the database to maintain — 2)
-D <Database Name>
[-Rpt <Output File>] [-To <Operator Name>]
D<Database Name> is the database to maintain
Rpt <Output File> is the full path of the report file
To <Operator Name> is the account to email a report to

3) CkDB | - CkDBNoldx will run dbcc checked or dbcc checkdb with the no index option

4) UpdSts will udpate the statistics on every table, index and statistic group in the database.

5) BkUpDB Path will back up the database — 5) BkUpDB <Backup Path> backs up the database.
BkUpLog <Backup Path> backs up the transaction log.

6) UpdOptiStats </% of Database to Sample> — 6) UpdOptiStats <% of Database to Sample> allows you to specify how much of the database to sample when generating statistics.

7) PlanId < GUID identifying the maintenance plan ... allows you to specify an existing database maintenance plan. The only thing from the plan that will be used is the list of database.

8) CrBkSubdir creates a sub-directory for storing the backup files.

9) ? generates a list of the parameter. — 9) S <Server Name> is the SQL Server to connect to.

If you want to use integrated security when you are using sqlmaint, you do not specify anything for the username and password.
If you are not using integrated security, you may need to include the username and password in double quotes.

High Performance SQL Server

Validate the output

After running sqlmaint, you should check the output to validate that it worked correctly and that any dbccs performed did not generate an error.
If it runs correctly, you will only see the output generated by the dbccs that shows what tasks were performed. When running sqlmaint, Microsoft recommends that you DO NOT use the dbcc newalloc command or the dbcc textalloc command. This is because they are planning on getting rid of the command.

Examples of sqlmaint.exe

Here are some examples of how you might use sqlmaint.exe to accomplish various maintenance tasks based on specific conditions.
This example of sqlmaint.exe will run dbcc checkdb on the Customer database using standard security.


sqlmaint -S MyServer -U "sa" -P "SaPwd" -D Customer -CkDB

This example performs an update statistics on all tables in the Sales databases using integrated security.


sqlmaint -S MyServer -U "" -P "" -D Sales -UpdSts

Using Sqlmaint.exe - Exercise

Before you continue, click the Exercise link below to check your understanding of the sqlmaint.exe syntax.
Using SQLmaint.exe - Exercise
The next lesson will show you how to use the database maintenance plan Wizard.