Truncating & Transaction Log

Truncating & Transaction Log

• Each database has its own transaction log ,for example the system table has syslogs
• The transaction log is appended to each time the database is modified
• Truncating the transaction log prevents the log from filling up which creates room for future growth

Fully & Minimally Logged Transaction

• Fully Logged Transaction

• Every recorded in the log
  • insert/update/delete
  • slow bcp
• Minimally Logged Transaction

• One row records the operation
  • select ...into
  • Truncate table
  • Fast bcp
  • write text
  • create index

Log grows during the dump,even with trunc log on checkpoint option

If we set the truncate log on checkpoint ,we cannot dump the transaction to flat file

The log cannot truncate because the checkpoint process occurs only at the start and completion of the database dump

Syntax: dump transaction <transaction name> on <device name>

dump transaction <database name> with_truncate_only

** Logptr column points to the first data page in the transaction log .this column is necessary for the dump tran with no_truncate command to function

** do not use dump tran with no_truncate command with a database that is in use

LOG contains

The following transaction fill up the log rapidy

• updating every row in a large table
• deleting a large table
• inserts based on sub query that returns a large no of rows
• bulk copying in a large table that has indexes

Truncating the Transaction Log

Dump Transaction

Makes a backup copy of the log and removes the inactive part in most cases

Dump transaction with no_truncate

The server makes a copy of the inactive (committed) transaction but doesn't remove those transactions from the log ,if repeated again the log will grow in size and eventually have to be purged(to clear) via dump transaction or dump transaction with truncate_only

Dump transaction with truncate_only

Removes the inactive part of the log without making a backup and logs de-allocation of inactive pages

Dump transaction with no_log

Dump transaction database name with no_log it clears both active and inactive transaction

Removes the inactive part of the log with making backup copy and without recording the procedure in the transaction log

Use this option only when you have totally run out of log space and cannot run a dump transaction command as usual

NOTE: you must dump the database immediately after performing either of these options .If corruption occurs while using dump transaction with no_log or dump transaction with truncate_only ,you will be unable to recover because there will be no log information