How to know whether transaction logs are being truncated or not

[Former Member]

Hi,

We are using Veritas Backup tool for Backups and restores on our MS SQL 2000.

One of our Veritas Tech has disabled the Truncation of Transaction Log Backup using the Job setup in Veritas. We want to confirm whether the Truncation is happening or not. I don't see any difference in the Transaction Log file size.

How to find out whether truncation is active or not?

Thanks

Vijay

[clas_hortien]

Hi,

normally the log gets truncated, when the log backup was successful. If you stop truncating the log during a backup, the log will grow and grow as long as diskspace is available.

If you mean, that you have switch from the simple recovery mode (formerly known as 'truncate log at checkpoint') to the full recovery mode you have to have a full database backup to stop the log truncation, as it makes no sense to keep the log until you don't have a valid database backup for restoring it to. So do a full database backup and then you should see the log to grow. A backup log then will free the log again. You can monitor the used space within the log with

dbcc sqlperf(logspace)

Best regards

Clas

[Former Member]

Hi Clas,

Thanks for your detailed help.

However, our Veritas Admin confirmed that the transaction log truncation has been switched off now. But, transaction logs are being backed up every 20 mins without truncation.

So, for every 20 mins, the transaction log data will be moved and the log file will be free even if the truncation is off. Am I right?

We want to switch of truncation and take a full offline databse backup to restore it in the standby server and configure log shipping after that. We experienced some issues with the LSN number mis-match earlier. I have found few forums which said that this can be resolved if the truncation is switched off and a full DB backup is restored.

Any help would be appreciated.

Thanks

Vijay

[clas_hortien]

Hi,

what do you mean with: transaction logs are being backed up every 20 mins without truncation. Is the log backed up, but not truncated (so it will constantly grow) ?

What does the command:

exec sp_dboption 'YourDatabasename'

returns ?

Regards

Clas

[Former Member]

Hi Clas,

The result of the T-SQL command exec sp_dboption 'YourDatabasename' is as below:

The following options are set:

Torn Page Detection

Auto Create Statistics

ANSI nulls

Auto Update Statistics

The log file size is growing now.

What should be the output if truncation is ON?

Thanks

Vijay

[clas_hortien]

Hi,

if you are in simple mode (truncation is on) you will see an entry like this:

The following options are set:

-

---

trunc. log on chkpt.

auto create statistics

auto update statistics

So, as the log is now growing, you have to run regular transaction log backups to keep it small. You can later apply these TA log backups to your restored database backup (keep the database in recovery mode when you restore it, only in this mode can you apply TA log backups).

Regards

Clas

[former_member185954]

hello vijay,

I have replied to your message here:

Regards,

Siddhesh

[Former Member]

Thanks for all your help on clarifying my questions.

Regards,

Vijay

[Nussi]

Vijay,

well - try it tactically.

restore your backup to a new database and have a look if the new database has a smaller log.

than you know it

regards

David