ppk on JavaScript, 1/e

Maintenance plans are a common way to detect problems in your database, correct the problems, and perform full and transaction log backups. To create a maintenance plan in Enterprise Manager, right-click Database Maintenance Plans under the Management group and select New Maintenance Plan. This opens the Database Maintenance Plan Wizard, which walks you through creating a plan with easy-to-follow steps.

After you finish using the wizard, several jobs are created for each item that needs to be scheduled. The wizard also adds an entry in the Database Maintenance Plans section of Enterprise Manager. Enterprise Manager reads from the sysdbmaintplans in the msdb database.

To execute the maintenance plan, you must execute the individual jobs in Enterprise Manager or use the sqlmaint tool (discussed next). After you execute the maintenance plan, it is logged in the sysdbmaintplan_history table, which is also in the msdb database. The easiest way to view the information from this table is to right-click the maintenance plan and select Maintenance Plan History. You are shown how long each step of the maintenance plan took to execute and any resulting errors.

Using sqlmaint

You can execute a maintenance plan from a command-line utility called sqlmaint, which is located in the \Program Files\Microsoft SQL Server\MSSQL\Binn directory. This utility allows you to execute some pieces of a maintenance plan without creating a plan at all. Here are some of the parameters you can use with this utility:

• -? Access help on sqlmaint.

• -S <server name> Server and instance to perform maintenance against. For a secondary instance, this parameter looks like ServerName\InstanceName.

• -U <login id> Login ID used to log in to the server specified in the -S parameter. If you don't specify this parameter, the utility uses Windows Authentication. You should enclose this login ID in double quotes to prevent errors in special instances with special characters.

• -P <password> Password for the login ID specified with the -U parameter. Again, enclose this password in double quotes.

• -D <database name> Database to run maintenance against.

• -PlanName <plan name> The maintenance plan if you want to execute an existing maintenance plan.

• -PlanID <GUID> Global unique identifier (GUID) of the maintenance plan if you want to execute an existing maintenance plan. You can determine what the maintenance plan's GUID is by querying the plan_id of the sysdbmaintplans table in the msdb database.

• -Rpt <path and filename> Sets the full path and filename of the report that is generated by running the maintenance plan. The report file is automatically version-controlled by a timestamp that is appended to the end of the file. For example, if you specify the -Rpt parameter to be set to C:\Program Files\Microsoft SQL Server\MSSQL\LOG\Northwind_Report.rpt, the file's final name would be similar to C:\Program Files\Microsoft SQL Server\MSSQL\LOG\Northwind_Report200103161202.rpt.

• -To <operator name> Sets the name of the operator who receives the report from the maintenance job.

• -HtmlRpt <path and filename> Similar to the -Rpt option, but this one generates an HTML report. These HTML report files are time stamped like the reports generated from the -Rpt option. The HTML report files are much more aesthetic than the standard report files.

• -DelHtmlRpt <number of days> Forces HTML reports to be deleted after this time interval in days.

• -RMUnusedSpace <percent free> Removes unused space for those databases that have the Autogrow option enabled (set by default). The number you specify here represents the percentage of free space you'd like to be available to the database after it is shrunk. If the database is already smaller than this percentage, the setting is ignored.

• -CkDB | -CkDBNoIdx These two options run the DBCC CHECKDB command against the database specified with the -D parameter. If you specify -CkDBNoIdx, the DBCC CHECKDB command is run with the NOINDEX option enabled, which will not check the indexes in the database.

• -CkAl | -CkalNoIdx These two options run the DBCC NEWALLOC command against the database specified in the -D parameter. If you specify -CkalNoIdx, indexes are ignored with the NOINDEX option.

• -CkCat Executes the DBCC CHECKCATALOG command against the database specified in the -D parameter.

• -UpdOptiStats <sample percentage> Updates the statistics for every table in the database specified in the -D parameter. If you have the database automatically update the statistics in your database options, this option is unneeded.

• -RebldIdx <fill factor inverse> Sets the amount of free space in the indexes after the rebuild is performed. For example, if you set this setting to 10, the indexes will be rebuilt with a fill factor of 90.

• -WriteHistory Specifies that a record will be logged in the sysdbmaintplan_history table in the msdb database.

• -BkUpDB <path> Performs a full backup against the database specified in the -D parameter. The backup is placed in the path you specify. If the -UseDefDir parameter is specified, SQL Server will use the default backup directory, which is \Program Files\Microsoft SQL Server\MSSQL\backup by default. The backup files are time stamped. For example, the Northwind database would be backed up to a file called Northwind_db_200303140000 if sqlmaint was run on 3/14/03 at midnight.

• -BkUpLog <path> This is the equivalent of the -BkUpDB parameter, but it backs up the transaction log.

• -BkUpMedia <DISK|TAPE> Specifies whether the database and log files are being backed up to a tape or disk.

• -DelBkUps <time interval> Specifies the time in days after which the backup files will be deleted in the backup directory.

• -CrBkSubDir Creates a subdirectory for each database backup. This is especially useful when you're trying to organize your files.

• -BkUpOnlyIfClean Performs a backup only if your database passes the maintenance checks (that is, -CkDB). I generally don't use this option, but keep enough iterations of backups where a corrupt database won't overwrite my good backups.

• -VrfyBackup Checks the backup to ensure it is in good state after the backup completes.

To execute a maintenance plan called 'DB Maintenance Plan1' using sqlmaint, you can use the following syntax:

sqlmaint -S xanadu -U "sa" -P "password" -PlanName "DB Maintenance Plan1" -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks

Using xp_sqlmaint

You can also execute a maintenance plan from T-SQL using the xp_sqlmaint system extended stored procedure. To execute the stored procedure, call it and add any parameters in quotes as shown here:

master..xp_sqlmaint '-S xanadu -U "sa" -P "" -PlanName "DB Maintenance Plan1" -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps 2weeks'