By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,491 Members | 3,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,491 IT Pros & Developers. It's quick & easy.

Backup log with no_log

P: n/a
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...en-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.
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

"A.M. de Jong" <ar****@wxs.nl> wrote in message
news:bd**********@reader08.wxs.nl...
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...en-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.

Jul 20 '05 #2

P: n/a
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 ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3

P: n/a
See comments inline.

"Arno De Jong" <an*******@devdex.com> wrote in message
news:3e*********************@news.frii.net...
Hi Simon,

So it means that it is not possible to restore my database to any time
after the last backup is taken ???
Right, because NO_LOG means all transactions after the full backup took
place are lost.
But the transactions after the log file is shrunken are still in the
transaction log file, aren't they ??
No - if you execute backup log ... with no_log then you lose transactions
completely.
So I would think that I still can restore up to any time ???
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.

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

Bye

Arno de Jong

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.