Monitoring Databases  «Prev  Next»

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
  1. Run DBCC checks
  2. Back up a database
  3. Back up a transaction log
  4. Update statistics
  5. Rebuild indexes
  6. 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.
1) SQL Maintenance 1 2) SQL Maintenance 2 3) SQL Maintenance 3 4) SQL Maintenance 4 5) SQL Maintenance 5 6) SQL Maintenance 6 7) SQL Maintenance 7 8) SQL Maintenance 8 9) SQL Maintenance 9
  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
  2. 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 noindex option
  4. UpdSts will udpate the statistics on every table, index and statistic group in the database.
  5. BkUpDB <Backup Path> backs up the database.
    BkUpLog <Backup Path> backs up the transaction log.
  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.
  8. CrBkSubdir creates a sub-directory for storing the backup files
  9. [-DelTxtRpt <time period> ]
[-DelHtmlRpt <time period> ]

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

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.

High Performance SQL Server