Database maintenance – Grass Valley NewsBrowse Desktop Browsing System Installation v.2.0 User Manual
Page 118
![background image](/files/813995/content/doc118.png)
118
NewsBrowse Installation and Configuration Guide
25 May 2004
Chapter 4 Recovery Planning
Database Maintenance
NewsBrowse utilizes the SQL full recovery model and a maintenance plan is essential
to keeping the database in working order. Not only does the database need to be
backed up but the accompanying transaction log needs to be backed up as well.
Failure to back up the transaction log can cause the database to become inoperable due
to the transaction log file growing too large.
The transaction log is responsible for keeping track of all the edits to data until it
reaches what is known as a checkpoint. Once the checkpoint is reached, the data
should be permanently committed to the database. Problems arise when this
checkpoint is reached, data is not committed to the database, and the transaction log
continues to grow. If the transaction log reaches the capacity of growth it can render
the database inoperable. In the event that the database has been rendered inoperable,
a manual truncation of the transaction log will need to be performed, as explained in
“Repairing a database that is unusable due to transaction log size” on page 118
.
Adopt the following practices to keep the database healthy:
• Daily monitor the growth of the transaction log daily, as explained in
determine the size of the transaction log” on page 119
• When necessary, manually back up the database and the transaction log, then
shrink the transaction log file to release disk resources to the operating system, as
explained in
“Manually controlling transaction log growth” on page 119
• Set up a database maintenance plan. This automatically backs up the transaction
log and the database. Refer to
“Setting up a database maintenance plan” on
Repairing a database that is unusable due to transaction log size
If the database is rendered inoperable due to the transaction log becoming too large,
it is highly likely that the transaction log has never been backed up, a database
maintenance plan has not been enabled on the system, or the SQL Server agent is not
running to implement your maintenance plan. The following steps should resolve the
problem:
1. Open SQL Query Analyzer
2. Make sure the database in question is selected in the top tool bar. (For this example
the ThomsonAM database will be used. The code will be the same for
Thomson_Ingest and Thomson_RulesWizard.)
3. First try to backup the transaction log without truncating it by running the
following command:
BACKUP LOG ThomsonAM TO Disk = 'filePath\fileName.trn'
Where filePath and fileName is user specified, (e.g. D:\Emergency
Backups\ThomsonAM_tran_12052003.trn) if the file does not already exist, you
will need to create it. If the transaction log is full it is likely that this command will
fail but it is important to try it to maintain database integrity.
4. Regardless if the previous command passes or fails, execute the following
command to clear the transaction log:
BACKUP LOG ThomsonAM WITH TRUNCATE_ONLY