Presents your 
SQL SERVER E-NEWSLETTER for May 20, 2003

<------------------------------------------->

USE SQLMAINT.EXE FOR ROUTINE MAINTENANCE

If you want to perform a specified set of maintenance operations on one
or more databases, check out the command-prompt utility SQLMaint.exe.
With SQLMaint.exe, you can run DBCC checks, back up databases, and update
statistics. These activities also generate reports that can be sent to a
text file, an HTML file, or an e-mail account.

The syntax for SQLMaint.exe contains lots of arguments, so be careful
when using it. In addition, SQLMaint.exe returns a 0 for a successful run,
but returns a 1 if an action fails, it encounters a general failure, or
there's a problem with the data.

Here's a quick look at the SQLMaint.exe syntax. (Remember that you must
separate parameters and their values by a space.)

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

In the following example, we perform a DBCC check on the Northwind
database located on MyServer, using the login of sa with a password of SaPwd.
The -CkAl and -CkCat arguments indicate that we'll run the Checkalloc and
Checkcatalog commands. Then we generate a text file and save it on the
C: drive in the MyReports folder.

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

If you want to run SQLMaint.exe but don't want to fool with a command
prompt, you can use the extended stored procedure xp_sqlmaint with switch
strings. Execute permissions for xp_sqlmaint default to the db_owner role
in the master database or the sysadmin fixed server role, but permission
to use xp_sqlmaint can be granted to others.

In the next example we use xp_sqlmaint to call SQLMaint.exe, perform
integrity checks, save a report to a file, and update the
msdb.dbo.sysdbmainplan_history. In the Query Analyzer, type the following:

EXEC xp_sqlmaint '-PlanID 02A52657-D546-11D1-9D8A-00A0C9054212 -Rpt
"C:\Program
 Files\Microsoft SQL Server\MSSQL\LOG\DBMaintPlan2.txt"
-WriteHistory  -CkDB
-CkAl'

For more information on the other arguments available with SQLMaint.exe,
please check Books Online.
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

----------------------------------------