October 27, 2009 at 12:26 am
Hi,
While shrinking the Transaction log file, we are using the below command
What will happen internally when i use this command?
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
Where does the backup getting stored over here
Normally we are using the below command
Backup Log <dbname> to Disk = <device_name> With NO_TRUNCATE
What will happen if i use TRUNCATE_ONLY and NO_TRUNCATE options?
In which situations we use either of the two?
Thanks in Advance
October 27, 2009 at 1:49 am
mahesh.vsp (10/27/2009)
Hi,While shrinking the Transaction log file, we are using the below command
What will happen internally when i use this command?
BACKUP LOG <dbname> WITH TRUNCATE_ONLY
Where does the backup getting stored over here
The backup doesn't get stored. That's what you're saying when you ask it to truncate only. Discard the log records without backing them up. This breaks the log chain, you will not be able to take log backups and you have no possibility of point-in-time recovery until another full/diff backup is taken.
This option should never be used, besides it's deprecated in SQL 2005 and gone in SQL 2008.
Please read through this - Managing Transaction Logs[/url]
Normally we are using the below command
Backup Log <dbname> to Disk = <device_name> With NO_TRUNCATE
That's a silly thing to do. You're backing up the log and telling SQL not to mark the space in the log as reusable. If you normally back up with that option, then your log files will grow and grow and grow, just as if you weren't taking log backups at all.
NoTruncate is generally used when backing up the log of a damaged database.
As per Books Online
NO_TRUNCATE
Specifies that the log not be truncated and causes the Database Engine to attempt the backup regardless of the state of the database. Consequently, a backup taken with NO_TRUNCATE might have incomplete metadata. This option allows backing up the log in situations where the database is damaged.
The NO_TRUNCATE option of BACKUP LOG is equivalent to specifying both COPY_ONLY and CONTINUE_AFTER_ERROR.
Without the NO_TRUNCATE option, the database must be online.
If the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2009 at 2:48 am
You would make a good primary school teacher :-D, nice reply btw
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 27, 2009 at 10:27 pm
Thanks 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply