Page 117 - DCAP508_DATABASE_ADMINISTRATION

Unit 7: Automating Administrating Tasks

Maintenance plans can be created to perform the following tasks: Notes
Reorganize the data on the data and index pages by rebuilding indexes with a new fill
factor. Rebuilding indexes with a new fill factor makes sure that database pages contain an
equally distributed amount of data and free space. It also enables faster growth in the
future.

Compress data files by removing empty database pages.

Update index statistics to make sure the query optimizer has current information about
the distribution of data values in the tables. This enables the query optimizer to make
better judgments about the best way to access data, because it has more information about
the data stored in the database. Although index statistics are automatically updated by
SQL Server periodically, this option can force the statistics to update immediately.

Perform internal consistency checks of the data and data pages within the database to
make sure that a system or software problem has not damaged data.

Back up the database and transaction log files. Database and log backups can be retained
for a specified period. This lets you create a history of backups to be used if you have to
restore the database to a time earlier than the last database backup. You can also perform
differential backups.

Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of
actions, and also the maintenance plans to run the jobs.
The results generated by the maintenance tasks can be written as a report to a text file, or written
to the maintenance plan tables, sysmaintplan_log and sysmaintplan_logdetail, in msdb. To
view the results in the log file viewer, right-click Maintenance Plans, and then click View
History.

Task Create a maintenance plan using the maintenance plan wizard for reorganizing the
data and index pages by rebuilding indexes with a new fill factor.

Maintenance plans only run against databases set to compatibility level 80 or higher. The
maintenance plan wizard does not display databases set to compatibility level 70 or lower.

7.5 Summary

Microsoft SQL Server allows you to automate administrative tasks. Using automated
administration to handle routine tasks and events frees your time to perform other
administrative functions.
Database Mail provides a robust, high-performance replacement for the most commonly
requested features of SQL Mail. Database Mail is designed to operate with SMTP servers,
and is tested with Microsoft SMTP servers.

Event notifications execute in response to a variety of Transact-SQL data definition language
(DDL) statements and SQL Trace events by sending information about these events to a
Service Broker service.

SQL Server Agent is a Microsoft Windows service that allows you to automate some
administrative tasks. SQL Server Agent runs jobs, monitors SQL Server, and processes

LOVELY PROFESSIONAL UNIVERSITY 111