sql server (alternate): Hi,

Every day we perform a full database backup.
(during the night).
Since we need no "additional" backups we had the problem that the
transaction log file grows and grows.
Therefore now just after the backup we perform a backup of the transaction
log with no log.
So we free up the space in the log file. (it truncates).

However, now we get this errormessage:

PRB: A "Database log truncated" Error is Logged in the Event Log When You
Try to Back Up the Transaction Log

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b818202





I am aware of this message but I think that it is basically a warning.
Am I correct ???

Arno de Jong, The Netherlands.

The message is a warning that your database is using the Full recovery
model, but you have truncated the log and so it will not be possible to
recover to a point in time, or following a system failure - you will have to
load your last full backup, and any changes after that are lost.

If I understand your description correctly, you do a full backup every
night, but no transaction log backups during the day. If so, you might
consider putting the database in the Simple recovery model, which means the
log will not grow so large, and you will not see these warning messages. The
disadvantage is that if the system fails, you will lose all modifications
since the last full backup, but it looks like you don't want or need fuller
recovery anyway.

The BOL topic "Selecting a Recovery Model" has a good explanation of the
differences between the various recovery models.

Simon

[quoted text, click to view]

Hi Simon,

So it means that it is not possible to restore my database to any time
after the last backup is taken ???
But the transactions after the log file is shrunken are still in the
transaction log file, aren't they ??
So I would think that I still can restore up to any time ???

Suppose I want still be able to restore up to any time (since last full
backup) AND I would like to shrink my transaction log file: what is the
alternative ???

Bye

Arno de Jong



*** Sent via Developersdex http://www.developersdex.com ***

See comments inline.

[quoted text, click to view]

Right, because NO_LOG means all transactions after the full backup took
place are lost.

[quoted text, click to view]

No - if you execute backup log ... with no_log then you lose transactions
completely.

[quoted text, click to view]

No - point in time recovery is only possible if you have backed up the
transaction log, and you have not truncated it with NO_LOG since the last
full (or perhaps differential) backup. Check out "How to restore to the
point of failure (Transact-SQL)", and "Recovering to a Point In Time" in
Books Online.

[quoted text, click to view]

Then you should consider using Full (or possibly Bulk-Logged) recovery
model, with regular transaction log backups during the day - look at the
topic "Transaction Log Backups" in BOL for an example.

BACKUP LOG always frees up space inside the log file, even without NO_LOG,
but doesn't make the log files any smaller physically. The space will get
re-used for logging future transactions, or you can physically shrink the
log files to reclaim the space. "Shrinking the Transaction Log" in BOL gives
a detailed explanation of logical vs physical space in the logs.

[quoted text, click to view]