Turn of logging on SQL maintenance plans

[Justin]

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?

[Hugo Kornelis]

On 10 Jul 2006 12:32:46 -0700, Justin wrote:

>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?

Hi Justin,

You can't turn off logging completely, but you can tell SQL Server to
overwrite parts of the transaction log that are not needed for restore
or repair in case of emergency. You do this by setting the recovery
model to simple.

BEWARE: By setting the recovery model to simple, you forfeit the ability
to restore your database to a point in time. To minimize potential for
data loss, you should switch back to full recovery and take a full
database backup (in that order!!) as soon as the maintenance job is
done.

--
Hugo Kornelis, SQL Server MVP

[Erland Sommarskog]

Justin (ju*************@hotmail.com) writes:

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, es****@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