Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kbk
Starting Member
34 Posts |
 Posted - 2009-04-21 : 14:35:40
|
| Does the query 'backup log with truncate only' actually backup the log?What is the difference in taking a transaction log backup and using this command?PVK |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-21 : 15:42:15
|
It does NOT backup the log. Only in a dev db (with SIMPLE recovery mode) should this be used to free up space in the log. From Books Online:quote:
[ NO_LOG | TRUNCATE_ONLY ] This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it. Used in only BACKUP LOG statements, performs a checkpoint to manually force the transaction log to be truncated. NO_LOG and TRUNCATE_ONLY are synonyms. Specifying a backup device is unnecessary because the log is not backed up. Under the simple recovery model, performing a checkpoint removes the inactive part of the log without making a backup copy. This truncates the log by discarding all but the active log. This option frees space, but risks possible data loss. After the log is truncated by using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the truncated portion of the log are not recoverable until the next database backup. Therefore, for recovery purposes, after using either of these options, immediately execute BACKUP DATABASE to take a full or differential database backup. ms186865.Caution(en-US,SQL.90).gifCaution: We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.
Be One with the OptimizerTG |
 |
|
|
kbk
Starting Member
34 Posts |
Posted - 2009-04-21 : 16:03:11
|
| Thankyou... So that means the command isnt equivalent to taking a transaction log backup right?PVK |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-04-21 : 16:37:47
|
| Nope, and you do not want to run that when you're doing transaction log backups for recoverability reasons.[url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-21 : 20:36:34
|
quote:
Originally posted by TG
It does NOT backup the log. Only in a dev db (with SIMPLE recovery mode) should this be used to free up space in the log. From Books Online:quote:
[ NO_LOG | TRUNCATE_ONLY ] This option will be removed in a future version of SQL Server. Avoid using it in new development work, and plan to modify applications that currently use it. Used in only BACKUP LOG statements, performs a checkpoint to manually force the transaction log to be truncated. NO_LOG and TRUNCATE_ONLY are synonyms. Specifying a backup device is unnecessary because the log is not backed up. Under the simple recovery model, performing a checkpoint removes the inactive part of the log without making a backup copy. This truncates the log by discarding all but the active log. This option frees space, but risks possible data loss. After the log is truncated by using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the truncated portion of the log are not recoverable until the next database backup. Therefore, for recovery purposes, after using either of these options, immediately execute BACKUP DATABASE to take a full or differential database backup. ms186865.Caution(en-US,SQL.90).gifCaution: We recommend that you never use NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log, because this breaks the log chain. Until the next full or differential database backup, the database is not protected from media failure. Use manual log truncation in only very special circumstances, and create backups of the data immediately.
Be One with the OptimizerTG
Don't need to use this if database is in simple recovery model. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-04-22 : 08:08:01
|
| Of course that's true...I guess what I've observed is occasions when there was some fatal error like tempdb expanding causing the server to run out of disk space during a large query. For some reason the normal checkpoint behavior was aborted and a database (like tempdb) was left with a "bloated" transaction log. My only point was that if the developer/dba cares about the data and uses a recovery mode other than simple then they probably shouldn't use this command.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
backup log with truncate only