Turn off the Transaction log

[Former Member]

All,

My client copy is really big.

How can I switch off the Transaction logs in SQL server whie doing client copy.

I DO NOT want Trans log keep increasing.

In oracle, I can turn off the archive logs.

Please advise.

Thanks again and hope to hear from you soon.

From,

PT.

[rolfc_weber]

Hi,

Under SQL Server you can't switch off transaction log completely.

But you can set you database in "SIMPLE" mode (properties of the database).

Because of recovery reasons you however should do at backup (at least a log-backup) right before you change the database mode, and after client copy has finished, and switched back to "FULL" mode, you have to do a full backup of the database.

Hope this helps

Regards

Rolf

[Former Member]

Just curious

Why are we doing backup twice after changing the mode ?Please advise.

From

PT.

[clas_hortien]

Hello,

the log backup on simple mode is not necessary. The full database after switching back to full is a must.

Please keep in mind, that even running on simple mode the log can be filled up, as all transactions are still written to the log. Commited transaction then can truncated from the log. But when you run a hugh transaction like a client copy, the log might grow as well. The log will be freed once the transaction commits or rolls back. And no, you can't split a client copy in several transactions.

Best regards

Clas

[rolfc_weber]

Yes,

But I would still do a log-backup just before switching to simple mode, because of the point in time recovery.

The last log-backup is the recovery point you could recover to if something goes wrong while in simple mode and you are forced to restore the database.

Rolf