well as any configuration data or scripts that would be used to reconstruct the
server. Test these backup plans extensively.
As mentioned in previous chapters about transaction logging, transaction
logs are the repository of changes made to the databases. But since they are
limited in size, they must be watched to prevent them from filling up. A full
log stops all modification to the database, and the only way to reliably
“prune” the logs (remove completed, checkpointed transactions) is to dump
them. Make sure that the logs are dumped on a regular basis and use threshold
management to avoid “log segment full” errors.
To verify how large the logs have grown, use the sp_spaceused stored
procedure or sp_helpsegment logsegment to monitor the log and its remain
Another task that is left to system administrators is to ensure that the data-
bases themselves are not completely full or that the space in a database is not
being misused. A database will grow over time as data is added to it. Even-
tually, it can grow to the point where it uses all its allocated space. To prevent
this from happening, use the sp_spaceused stored procedure to verify the
unused space in the database. When the database approaches full, more space
can be added or data can be purged from the database.
It is also the administrator’s job to ensure that the space is not misused.
Remember, databases can be enlarged but not easily shrunk. Try to make sure
that the databases are adequately sized with room to grow.
Note that you can now configure databases for automatic expansion.
Some shops do this so that their production environments don’t have sudden
problems; others do not so that they know when they’ve miscalculated and
can address the issues that made the databases grow unexpectedly.
From time to time, it may become necessary to attempt a complete rebuild of
an Adaptive Server. In order to recreate the server, it is essential to have
scripts ready that can perform the installation, configuration, and population
of the databases. Always keep up-to-date scripts with all database definition
information, from disk inits through object creation. Many of these can be
created retroactively with third-party reverse engineering tools, or the
defncopy utility. Keep the scripts on a different machine than the production
Chapter 15: Preventative Maintenance Regimen