Justin (justinmiyashiro@hotmail.com) writes:
Quote:
I am running a SQL maintenance job on a 40 GB database which performs
optimizations by re-orginizing data and indexes pages. After the job
is finished, a separate job peforming a SQL transaction log backup is
run on the same database, which produces a 30 GB transaction log backup
file. Is there any way to turn off logging during the maintenance
plan, so that when the transaction log backup occurrs it will not
produce a large backup file?
That depends. If the database goes bad in the middle of the day, because
of hardware error, or a serious human error, what is your requirement for
recover from the disaster? Are you content with restoring from the
the most recent full backup? Or do you want to be able to recover to a
point as close to the disaster to minimise data loss?
If the full backup is OK for you, then the answer is simple. Put the
database into simple recovery and forget all about the transaction log.
If you want up-to-the-point recovery, you want to avoid turning off
the transaction log, because if you do you break the log chain, and
until you have run a new full backup, you don't have the option to
recover to point in time.
What you could try to keep down the transaction log in size during
maintenance is to switch to bulk-logged recovery. This is a variant
of full recovery that logs less for some bulk operations like index
rebuilds.
You could also consider setting up a more intelligent job for reindexing.
There is little need to reindex a table with low fragmentation. DBCC
SHOWCONTIG can be used to examine the fragementation for a table.
--
Erland Sommarskog, SQL Server MVP,
esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
